CODEWITHSUNDEEP
sqlsql-serversubquery
John
John

Reputation: 11

Select all matching rows if 1 row value is true with SQL

I am new to SQL so I am not sure if I have used the correct terminology when searching but I imagine this has already been asked. If not, here we go.

Take the example table:

Cart    Item    Packer
Cart 1  Bananas Bob
Cart 1  Coffee  Bob
Cart 1  Pizza   Bob
Cart 2  Apples  Bob
Cart 2  Peaches Bob
Cart 2  Berries Bob
Cart 1  Soda    James
Cart 1  Bread   James

Then I want to see if coffee was put into a cart, what all of the things that packer put into that cart were like this:

Cart 1  Bananas Bob
Cart 1  Coffee  Bob
Cart 1  Pizza   Bob

I had tried a query like:

select cart, item, packer
from table
where packer in (select packer from table where item = 'Coffee')

But then I usually end up with cart 2 results as well since, I imagine, when the query looks for cart 2 and bob shows as having packed coffee ever, it selects that result. Then when I replace packer in the subquery with cart # but then I get James's results too. I even tried subquery in a subquery like:

    where packer (in select packer from table where item in(select item 
from table where item = 'Coffee'))

I just cannot figure out how to isolate the results for cart 1's packer who packed coffee.

Upvotes: 1

Views: 1858

Answers (4)

Sirmyself
Sirmyself

Reputation: 1564

Alternatively, you can use a subquery, which may be easier to setup and debug eventually :

set @itemSearch = "coffee"; -- variable to make the query more easy to change

SELECT t.cart, t.item, t.packer
  FROM (SELECT cart, packer
           FROM TABLE_1
           WHERE item = @itemSearch
  ) AS sub1
  INNER JOIN TABLE_1 t ON ((sub1.cart = t.cart) AND 
                           (sub1.packer = t.packer))

here's the link for the SQL Fiddle I made with that exemple. I added a line with coffee to show this query will work if there are two carts with coffee.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269943

Vamsi's answer is correct and the most general (and I have upvoted it). However, some databases support in with tuples, so your approach can be extended.

The key point is that you want the pair cart/packer:

select cart, item, packer
from table
where (cart, packer) in (select cart, packer from table where item = 'Coffee');

This is the closest version to what you are doing.

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

select p1.* 
from packer p1 
join packer p2 
  on p2.packer = p1.packer 
 and p2.cart   = p1.cart 
 and p2.item   = 'Coffee'

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use exists with matching cart and packer conditions to do this.

select cart, item, packer
from table t1
where exists (select 1 
              from table t2
              where t1.cart=t2.cart and t1.packer=t2.packer and t2.item = 'Coffee')

Upvotes: 2

Related Questions