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)

more