Re: Query for commonalities with cross check

[Peter Brawley]

> I need a query with which I can find users who have chosen items
> in "y_id" that are present in my "allowed_ids".

select distinct a.user_id, a.y_id, b.allowed_ids
from t a
join t b on a.user_id=b.user_id and a.y_id=b.allowed_ids

> in the same time I want to compare my selected "y_id" items
> with their "allowed_ids". And only if there is at least
> 3 match I want to get the corresponding user_id.

select a.user_id, a.y_id, b.allowed_ids
from t a
join t b on a.user_id=b.user_id and a.y_id=b.allowed_ids
group by user_id,y_id,allowed_ids
having count(*) >= 3;