Posted on 2005-04-20
Last Modified: 2010-03-19
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

SELECT     Contact_Record.client_id, Ethnic_grp.Ethnic_category
FROM         client INNER JOIN
                      Contact_Record ON = 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...
Question by:paulo111
    LVL 6

    Accepted Solution

    SELECT    Ethnic_grp.Ethnic_category, COUNT(Contact_Record.client_id)
    FROM       client LEFT JOIN
                    Contact_Record ON = Contact_Record.client_id INNER JOIN
                    Ethnic_grp ON client.ethnic_origin = Ethnic_grp.Code
    GROUP BY ALL Ethnic_grp.Ethnic_category
    HAVING COUNT(Contact_Record.client_id)=0  --this line is optional to get only those with 0 contacts
    LVL 26

    Expert Comment

    Another option is

    SELECT Ethnic_category FROM Ethnic_grp EG
          select 1 from FROM Contact_Record CR INNER JOIN client C ON = CR.client_id
          WHERE C.ethnic_origin = EG.Code
    LVL 28

    Expert Comment

    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

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now