Query question, please help!! [Archive]

Cahn00

04-28-02, 18:07

Hi, I am a newbie at doing sql, I need to write a query that finds the total number of people who has each item.

Below are the tables I have:

Create Table Item (
Item_Name Varchar(20) Primary Key
);

Create Table Person (
Person_Id Integer Primary Key
);

Create Table Item_Set (
Owner_Id references Person(Person_Id),
Item_Owned references Item(Item_Name),
Primary Key (Owner_Id, Item_Name)
);

The output expected would be something like:

Item_Name NO_People_Have
-----------------------------------------------
apple 3
orange 12

Please show me how to write a query like that in sql, help me, thanks!

alligatorsql.com

04-29-02, 06:54

Hello,

first let me say, that it´s very helpful when the primary fields and the fields that references the primary key has the same name - (that is the way I would create the table - but anyway ... you can name it like you want)

Here is the requested SQL statement

SELECT DISTINCT(i.item_name),
COUNT(is.owner_id)
FROM item i, item_set is
WHERE i.item_name = is.item_owned

I did´nt have tested it :)

Hope this helps

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com