#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    390
    Rep Power
    10

    Basic Query Problem


    This query seems really easy but I'm out of practice

    Basically, I need to find people who have ordered 4 particular products.

    Table 1 - person
    id
    name

    Table 2 - order
    id
    personid
    name

    I tried doing the following but it didn't work:

    Code:
    Select person.name from person inner join order on person.id = order.personid where order.name = "bike" and order.name="pencil" and order.name = "book" and order.name = "soda";
    It returns nothing yet I have 2 people who have ordered all 4 of these items.

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    it doesn't work because the WHERE clause evaluates each row by itself, and the same column value cannot be equal to 4 different things simultaneously

    what you're looking for is four different rows
    Code:
    SELECT person.name 
      FROM person 
    INNER
      JOIN [order] 
        ON [order].personid = person.id 
       AND [order].name IN ('bike','pencil','book','soda')
    GROUP
        BY person.name
    HAVING COUNT(DISTINCT [order].name) = 4
    two comments...

    first, ORDER is a reserved word, so you have to escape it (better would be to rename the table)

    second, strings are delimited by the single quote, not the doublequote

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    390
    Rep Power
    10
    Thanks for the quick response. I was thinking that was my problem but I always have a difficult time when using HAVING.

    I tried your query but the "AND" did not work so I replaced it with "WHERE" and the "DISTINCT" rendered an error so I removed it.

    So with the revised query I have
    Code:
    SELECT person.name 
      FROM person 
    INNER JOIN [order] 
        ON [order].personid = person.id 
    WHERE  [order].name IN ('bike','pencil','book','soda')
    GROUP
        BY person.name
    HAVING COUNT([order].name) = 4
    I received a result of one name returned which I think is right.

    Many Thanks
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by loulou
    I tried your query but the "AND" did not work so I replaced it with "WHERE" and the "DISTINCT" rendered an error so I removed it.
    what version of SQL Server are you running???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo