paulo111
asked on
Count
Not sure if this is possible, but what I need from the following stored procedure is a list of all the ethnic categories
that havent had any contacts... The ethnic code is simply letter such as A,B,C but what I need are the counts of contacts for clients in particular ethnic categories
for example
WHITE 0 Contacts
ASIAN 0 Contacts
BLACK 1
SELECT Contact_Record.client_id, Ethnic_grp.Ethnic_category
FROM client INNER JOIN
Contact_Record ON client.id = Contact_Record.client_id INNER JOIN
Ethnic_grp ON client.ethnic_origin = Ethnic_grp.Code
Is this possible, if so can someone show me how its done...
that havent had any contacts... The ethnic code is simply letter such as A,B,C but what I need are the counts of contacts for clients in particular ethnic categories
for example
WHITE 0 Contacts
ASIAN 0 Contacts
BLACK 1
SELECT Contact_Record.client_id, Ethnic_grp.Ethnic_category
FROM client INNER JOIN
Contact_Record ON client.id = Contact_Record.client_id INNER JOIN
Ethnic_grp ON client.ethnic_origin = Ethnic_grp.Code
Is this possible, if so can someone show me how its done...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this:
SELECT A.Ethnic_Category, COUNT(*)
FROM Ethnic_grp A LEFT OUTER JOIN Client B
ON A.Code = B.Ethnic_Origin
GROUP BY A.Ethnic_Category
SELECT A.Ethnic_Category, COUNT(*)
FROM Ethnic_grp A LEFT OUTER JOIN Client B
ON A.Code = B.Ethnic_Origin
GROUP BY A.Ethnic_Category
SELECT Ethnic_category FROM Ethnic_grp EG
WHERE NOT EXISTS(
select 1 from FROM Contact_Record CR INNER JOIN client C ON C.id = CR.client_id
WHERE C.ethnic_origin = EG.Code
)