CODEWITHSUNDEEP
sqlsqlitesearchtags
Wicia
Wicia

Reputation: 565

SQL - Tags searching query

I have following table in my database:

ID  name
1   x
2   x
3   y
1   y
1   z

Now I want to select only this objects (ID's) which has both 'x' and 'y' value s tag name. In this case this will be only record with ID = 1 because sought values set ('x' and 'y') is subset of this record possible names set ('x', 'y' and 'z').

How to write a SQL query?
Thanks for help :)

Upvotes: 1

Views: 1139

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270361

One method uses aggregation:

select id
from t
where name in ('x', 'y')
group by id
having count(*) = 2;

If you care about performance you might want to compare this to:

select id
from t tx join
     t ty
     on tx.id = ty.id and tx.name = 'x' and ty.name = 'y';

The first version is easier to generalize to more tags. Under some circumstances, the second might have better performance.

Upvotes: 3

Related Questions