🔒 Locked

[SQL] Help with duplicates

Started by chbrules Aug 26, 2005 at 4:53 PM 3 replies 350+ views

Original Post

Aug 26, 2005 at 4:53 PM

So I'm doing a query in MS SQL. I'm trying to pull address lists for mailing. The issue is, we have multiple contacts for the same company we want to send mail to, and we just want one contact from one company. So my question is, how do I tell the SQL server to just send me back 1 contact to the company instead of all of them? Thanks!

nts

Aug 26, 2005 at 4:59 PM

SELECT DISTINCT

Quote:

The DISTINCT keyword is used to return only distinct (different) values.

chbrules

Aug 26, 2005 at 5:00 PM

That's what I'm using already, doesn't work.

nts

Aug 26, 2005 at 5:16 PM

Quote:
Original post by chbrules
That's what I'm using already, doesn't work.

Ok, can you post your query?

You can also look into specifing a LIMIT and/or using subqueries in your WHERE clause (maybe with EXISTS/NOT EXISTS, ANY, IN, SOME).

chbrules

Aug 26, 2005 at 5:23 PM

There is no LIMIT in MSSQL. Here's my query:

SELECT DISTINCT c1.Company,c1.Contact,c1.Address1,isnull(c1.Address2,'') Address2,c1.City,c1.State,c1.Zip,c2.contsuprefFROM contact1 c1inner join contsupp c2 on c1.accountno = c2.accountnoWHERE c2.contact = 'E-mail Address'AND c2.contsupref != 'NULL'AND c1.Contact != 'NULL'AND c1.Address1 != 'NULL'AND c1.City != 'NULL'AND c1.State != 'NULL'AND c1.State = 'CA'AND c1.Zip != 'NULL'AND c1.country = 'USA'AND(c1.title LIKE '%Council%'OR c1.title = 'city clerk'OR c1.title = 'city manager')

The 'contsupref' field is the only unique field preventing the DISTINCT from working. Each duplicate contact may have a different email. I tried it without the email and all the duplicates are gone.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.