MySQL: select from table A if the ID matches 2 (or arbitrary n) rows in table B

Question

The scenario is pretty straight forward: I have the content in table A and the tags for the content in table B:

Table A: +----+-------+-... | id | title | ... +----+-------+-... Table B: +------+-----+ | id_A | tag | +------+-----+ 

I want to select all content lines in A that have the tag 'foo':

 SELECT A.* FROM A, B WHERE A.id = B.id_A AND B.tag = 'foo' 

So far it has been easy.

My problem: how can I select content lines that have the tag tag 'foo' and 'bar'? In particular, how can I select strings with n tags 'foo', 'bar', ... for arbitrary n > 1 ?

The solution would be to join B n times, but this is bad, and I assume it is not very effective.

Since I use MySQL, PHP and PDO with prepared statements, if there is a solution that goes without the necessary string concatenation (for example, in the "Join n times" solution) in the PHP part, this would be my favorite.

Answer 1

 SELECT * FROM a WHERE ( SELECT COUNT(*) FROM b WHERE b.id_a = a.id AND b.tag IN ('foo', 'bar', 'baz') ) = 3