As an example of what I'm looking for, consider the following example Fruits table
id user_id fruit type 1 43 apple fresh 2 87 watermelon fresh 3 99 apple fresh 4 43 pineapple dried 5 60 pineapple dried 6 60 lemon fresh 7 99 kiwi fresh 8 43 mango fresh
I want to find all the records for all users that have both "apple" AND "dried" in the same table (not necessarily in the same row at the same time). The same user must have both conditions.
What I'd like returned is:
id user_id fruit type 1 43 apple fresh 4 43 pineapple dried
I've tried several combinations of self joins:
SELECT distinct f1.*, f2.* FROM orders f1 FULL JOIN orders f2 ON f1.user_id = f2.user_id WHERE f1.fruit ILIKE 'apple' AND f2.type ILIKE 'dried'
but haven't been able to get it quite right.
This is for a Rails application so I'd love to be able to do this with ActiveRecord but I will be happy with plain SQL, if need be.
Any suggestions for how to do this? TIA
This should do it for you:
SELECT * FROM Fruits WHERE (Fruit = 'apple' OR Type = 'dried') AND EXISTS (SELECT NULL FROM FRUITS F1 INNER JOIN Fruits F2 ON F1.User_ID = F2.User_ID AND F1.Fruit = 'apple' and F2.Type = 'dried' WHERE Fruits.user_ID = F1.User_ID)