CODEWITHSUNDEEP
mysqlsqlintersectionself-join
minchiya
minchiya

Reputation: 597

sql Better way than intersection of inner-join

We have a table of 2 columns : ObjectName and ObjectColor. It is a many to many table. And we have a given set of colors {x, y, z,...}

We want to select the list of ObjectName that exists with all those given colors.

if we have N colors, I can imagine an sql query with N-1 intersection on this same table. Or we can also imagine N-1 self-joins on that table.

Is there a better solution ? Is there a particular solution with MySQL ?

THINKS

Upvotes: 0

Views: 91

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Here is one method:

select objectname
from t
where objectcolor in (x, y, z)
group by objectname
having count(*) = 3;

You need to adjust the "3" to match the size of the list.

Upvotes: 3

Related Questions