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.sqlsql-serversubquery
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')
select p1.* from packer p1 join packer p2 on p2.packer = p1.packer and p2.cart = p1.cart and p2.item = 'Coffee'
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
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.
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.