Criteria question

[bnageshrao]

Hi

I have two tables one is called lead and another one is called contact. Companyid is the key in both tables. For each lead there can be either one lead or more than one lead. I would like to find out lead with more than one contacts. I did the grouping based on company ids and pulled the contact names. It shows for each companyid either one or more contacts. What criteria I should use to get just company with more than one contacts. Do I have to use Group By and along with other criteria like having etc. I just dont know the syntax. If some one can shed some light into this, I will be gratefull, thanks in advance.

 

[GingerR]

This assumes your table is called Contact
One Field name is CompanyID
Another Field name is ContactID


SELECT Contact.CompanyID, Count(Contact.ContactID) AS ContactCount
FROM Contact
GROUP BY Contact.CompanyID
HAVING (((Count(Contact.ContactID))>1));

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

If you want all the contact names of all leads with more than one contacts:
SELECT L.*, C.*
FROM (lead AS L
INNER JOIN contact AS C ON L.Companyid = C.Companyid)
INNER JOIN (SELECT Companyid FROM contact GROUP BY Companyid HAVING Count(*)>1
) AS G ON L.Companyid = G.Companyid

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886