Jump to content
  • Advertisement
Sign in to follow this  
chbrules

[SQL] Distinct

This topic is 4849 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I'm using MS SQL fyi. I know what Distinct is and what it does, returns only unique results, but I don't think it's working on this larger query I'm doing. I'm going to go ahead and post my query, but a lot of it is irrelivent, I'm posting it all in hopes you might be able to pinpoint why it's not returning only distinct results:
SELECT DISTINCT isnull(c1.[Contact],''),c1.[Company],c1.[Address1],c1.[City],c1.[State],c1.[Zip],c2.[latestlsapexpiredate],c3.[organizationname]
FROM [Contact1] c1
inner join Compulink.dbo.CustomerProfile c2 on c2.companyid = c1.key4 /* Gets LSAP */
inner join Compulink.dbo.CustomerName c3 on c2.varid = c3.companyid /* Get Var Names */
WHERE c1.[Address1] != 'NULL'
AND c1.[City] != 'NULL'
AND c1.[State] != 'NULL'
AND c1.[Zip] != 'NULL'
AND c1.[Zip] != '*'
AND c1.[State] != '*'
AND c2.[latestlsapexpiredate] >= '2/7/2005'
AND c2.[latestlsapexpiredate] <= '7/7/2005'
ORDER BY c1.[Company] ASC
Now all I'm trying to do here is pull mailing list information from our databases across various tables using JOIN. We have multiple contacts for a single company, but I only want one of them to be returned. Problem is, DISTINCT (if that is the problem even), isn't doing that. I get returned all the instances of a company with the contact. Is there some sort of unique() funciton that will only return a single result instance of a column with nondistinct values? Edit: Actually distinct is pulling a couple off the list, but still, the problem is there.

Share this post


Link to post
Share on other sites
Advertisement
I think the problem is that you assume DISTINCT is only working on the first column when actually it works on all the columns. If you have multiple c1.[Contact] but one of the other columns is different (probably c2.[latestlsapexpiredate]), you will get another row.

If you understand while the DISTINCT is working like it is, it might help with a solution. For example, if the c2.[latestlsapexpiredate] is the column causing you to get duplicate rows, which c2.[latestlsapexpiredate] value would you want if DISTINCT worked like you expected?

Share this post


Link to post
Share on other sites
Sorry about this, I figured out my entire problem was an entirely different issue. =/

Thanks though.

Share this post


Link to post
Share on other sites


On a side note, you are comparing columns with the string literal 'NULL'. NULL is not a string it is a value that is never equal to anything. To test for columns being non-NULL you should use "column-name IS NOT NULL".

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!