DISTINCT clause problem

[LesStockton]

I've got a problem with getting the DISTINCT clause here working:

select Distinct(T_Contact.ContactID),
LastName, FirstName, T_Contact.ActiveInd, ContactTypeDescription,
T_ContactType.ContactTypeID, T_Organization.OrganizationID, OrganizationName,
City, State, HomePhone
from
T_Contact
join T_ContactTypeContact on T_Contact.ContactID = T_ContactTypeContact.ContactID
join T_ContactType on T_ContactTypeContact.ContactTypeID = T_ContactType.ContactTypeID
join T_OrganizationContact on T_Contact.ContactID = T_OrganizationContact.ContactID
join T_Organization on T_OrganizationContact.OrganizationID = T_Organization.OrganizationID
where 1=1
AND (T_Contact.ActiveInd = 1)
AND (T_Contact.OwnerCompany = 1) ORDER BY LastName, FirstName

For some reason, I get duplicate ContactIDs. I'd like to get unique ContactIDs, which essentially give me unique first,last name.

 

[jbenson001]

DISTINCT does not apply to one column, it applies to all of the columns in the SELECT.

Jim

 

[LesStockton]

So is there a way to accomplish what I want? Another way, maybe?
A select within another select?
If so ... please ...

 

[jbenson001]

Can you show some sample data of the dups you are getting...?

 

[SQLSister]

The method (assuming you have an id column which it looks like you do. Is ot selct the distinct Id column as a derived table then join it to the original table(s) to get the other columns. Be aware that if you are joining to related tables and need a column from that table, you may still still end up with multiple lines for one id unless you specify which particular record inteh related table you need (usually using max() or Min()). Code sample:

select t1.idfield, t1.field1, t1.field2, t2.max(field3) from table1 t1
join table2 t2 on t1.idfield, t2.idfield
join (select distinct idfield from table1 where field1 >100) a 
on t1.idfield = a.idfield

Of course your derived table used to get the distinct id fileds may involve joins.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief