Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002
    Posts
    1

    Unanswered: Query question, please help!!

    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!

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •