March 6, 2006 at 2:25 pm
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
March 6, 2006 at 2:28 pm
what happens if you run this query?
select Distinct
ContactID,
LastName,
FirstName,
ActiveInd,
ContactTypeDescription,
ContactTypeID,
OrganizationID,
OrganizationName,
City,
State,
HomePhone
from
(
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 )
Lowell
March 6, 2006 at 2:29 pm
Distinct only works on the entire row, regardless of where you place parentheses.
Your issue is cardinality of data. The fact that you have a table named T_ContactTypeContact indicates that a Contact can have 2 or more ContactTypes, and if you join to this *and* pull in the ContactTypeDescription in the resultset, you're always going to get duplicated ContactID's in the results.
If you only want 1 record per ContactID, what are your requirements for ContactTypeDescription if a Contact has 2 or more ContactTypes ?
March 6, 2006 at 2:31 pm
You should figure out which of your joins is causing the duplication (as that's most likely the issue), and modify your query to remove it....
For example, if one of your contacts is in two organizations, his contactid would be in there twice, after the join to that table, and you would see different values in the fields returned.
March 6, 2006 at 2:36 pm
Well, there might be a case in my stored procedure, where I am supplied the Organization, but nothign else and so would query for the contacts. I guess the query isn't specific enough. After looking at it all this time, I'm not sure what to change to make it more specific to eliminate the issue.
March 6, 2006 at 4:28 pm
As an earlier commenter suggested, your problem is likely cardinality (of course we cannot know for sure, as we cannot see the data).
Look to see if contact->organization is 1:1 or 1:M (one-to-many).
Eg, see if this gives you any hits, showing that it is 1:M:
select c.ContactID, count(*)
from T_Contact c
join T_OrganizationContact oc
on c.ContactID = oc.ContactID
join T_Organization o on oc.OrganizationID = o.OrganizationID
group by ContactID
having count(*)>1
If it does, you cannot list organization like that.
Similarly check contact type for cardinality.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply