SQL ManyToOne condition-CodePudding

But if I add one more for my query (that not related to this), for example:

I should get nothing

(but it needs to be workable :D)

CodePudding user response：

You can do a GROUP BY, and use HAVING to make sure all desired tag_id's are there:

SELECT sp.perfume_id
FROM sp_tag_to_perfume sp
WHERE sp.tag_id IN (2070, 127)
GROUP BY sp.perfume_id
HAVING COUNT(DISTINCT sp.tag_id) = 2; -- number of tag_id values (in this case 2070 and 127)

CodePudding user response：

This can get all the columns and data containing perfume id 199.

Select * from sp_tag_to_perfume sp where sp.perfume_id=199;

And from tag_id...

Select distinct perfume_id from sp_tag_to_perfume sp where sp.tags_id=127;

You can use distinct keyword to get unique values of the column.