SQL Question

From: Brady D'Andrea <bradyd_at_ti.com>
Date: 1996/05/19
Message-ID: <4nm320$6ip_at_newsboy.mtc.ti.com>#1/1

Hi,

For the life of me, I can't figure out the proper SQL statement to return the information I need. I'm searching a table based on two criteria. I need a count of all rows returned that match the second criteria grouped by the first critera, but I need items from the first criteria that don't match the second to return a count of 0

Using the following table as an example:

   TABLE A:      CITY      PERSON      TOOL
                 ------    ------      ------
                 DALLAS     Mike       Hammer
                 DALLAS     Kim        Hammer
                 DALLAS     John       WRENCH
                 DALLAS     Kim        Hammer
                 HOUSTON    Bill       Hammer

The best SQL I can come up with is:
        SELECT person, COUNT(tool) FROM A
        WHERE city IN ('DALLAS') AND tool IN ('Hammer')
        GROUP BY person;

(I need the IN() for my queries since I'll be searching for several items)

This produces the following output:

        PERSON     COUNT(TOOL)


        ---------- -----------

        Kim                  2
        Mike                 1

But what I need is:
        PERSON     COUNT(TOOL)


        ---------- -----------

        John                 0    <--- I need this zero value
        Kim                  2
        Mike                 1
                 

Any advice on generating the proper SQL statement would be MUCH appreciated!

Thanks in Advance,
Brady
bradyd_at_ti.com Received on Sun May 19 1996 - 00:00:00 CEST