Basic Query Problem

Dev Shed Forums Sponsor:

June 15th, 2012, 10:16 AM

loulou

Contributing User

Join Date: Sep 2005

Posts: 386

Time spent in forums: 4 Days 20 h 34 m 59 sec
Reputation Power: 9

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

June 15th, 2012, 10:31 AM

r937

SQL Consultant

Dev Shed God 43rd Plane (26000 - 26499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,442

Time spent in forums: 3 Months 1 Week 2 Days 14 h 15 m 54 sec
Reputation Power: 4141

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

June 15th, 2012, 10:50 AM

loulou

Contributing User

Join Date: Sep 2005

Posts: 386

Time spent in forums: 4 Days 20 h 34 m 59 sec
Reputation Power: 9

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

June 15th, 2012, 11:17 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,442

Time spent in forums: 3 Months 1 Week 2 Days 14 h 15 m 54 sec
Reputation Power: 4141

Quote:

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???

Basic Query Problem

Developer Shed Advertisers and Affiliates