MySQL: Select all rows with the same ID, if a column from a joined table contains x

Question

(Giving an extremely simplified sample here, for the sake of clarity.)

I have a many-to-many relationship in a database (MySQL with Doctrine2/Symfony2 entities, but i'd like to know the answer in plain SQL), with a simple 2-column "join table" in between:

item
iditemname
1 Cat
2 Mouse

tag
idtagname
1 Predator
2 Prey
3 Likes Cheese
4 Flying

item_tag
item_idtag_id
1          1
1          3
2          2
2          3

The standard join query for this:

SELECT itemname, tagname FROM item
JOIN item_tag ON  item_tag.item_id = item.id
JOIN tag ON item_tag.tag_id = tag.id

gives me all the data i'm interested in, with certain rows being double of course:

item
itemnametagname
Cat          Predator
Cat          Likes Cheese
Mouse     Prey
Mouse     Likes Cheese

Now I need to filter this with a simple WHERE statement, so I add something like this:

WHERE tagname = 'prey'

But that only returns a single row of course. I need to get all rows for all items which have a 'prey tag' — so all the rows for the mouse item. The fact that the mouse likes cheese is a pretty important detail to know when you are searching for prey!

The point is of course to enter a search query for a certain tag name (without knowing the item names in advance), but once it returns a set of items, I would like to see all the other tags as well for the items in that set.

Is this possible with one query, or would I be forced to re-query to grab the remaining associated tags?

I assume this is just a minor typo, but the tag for 'flying' should have id 4. — AdamMc331, Jul 02 '15 at 13:27

score 1 · Accepted Answer · answered Jul 02 '15 at 13:23

1

You can get the item_id from each prey like this:

SELECT it.item_id
FROM item_tag it
JOIN tag t ON t.id = it.tag_id AND t.tagname = 'prey';

Then, you can use that as a subquery in the `WHERE clause of your original query to get all rows for those items:

SELECT i.itemname, t.tagname
FROM item i
JOIN item_tag it ON it.item_id = i.id
JOIN tag t ON t.id = it.tag_id
WHERE i.id IN(
   SELECT it.item_id
   FROM item_tag it
   JOIN tag t ON t.id = it.tag_id AND t.tagname = 'prey');

Here is an SQL Fiddle example.

answered Jul 02 '15 at 13:23

AdamMc331

16,492
10
71
133

Still having lots of trouble with the Doctrine Querybuilder version of this (because the "intermediate" table isn't actually defined as an entity there, that's all abstracted away) But the SQL version is quite helpful in attacking this problem, currently giving this a try: http://stackoverflow.com/questions/6637506/doing-a-where-in-subquery-in-doctrine-2. Breaks my head to figure this out for many-to-many entities though. – okdewit Jul 02 '15 at 14:37
1

Managed to figure it out! I included my DQL/Doctrine2 solution in a separate answer. Couldn't have done it without your help! – okdewit Jul 02 '15 at 16:20
@Feroxium glad you figured it out! – AdamMc331 Jul 02 '15 at 17:15

score 0 · Answer 2 · answered Jul 02 '15 at 16:19

Thought I'd also include my Symfony2 / Doctrine solution, based on McAdam331's answer for SQL.

I used a second query builder instance in the item repository for the subquery. You just need to use different indexes than used in the main query builder, of course.

$qb = $this->createQueryBuilder('i');
$sub = $this->createQueryBuilder('subi');

$search = $qb->expr()->orX(
    $qb->expr()->like('i.name', ':s'),
    $qb->expr()->in('i.id', 
        $sub->select('subi.id')
        ->join('subi.tags', 'subt')
        ->where($sub->expr()->like('subt.tagname',':s'))->getDQL()
    ),
    ... // various other search expressions are included in the OR clause
)

$qb->select('i','t')->join->('i.tags','t') // etcetera
   ->where($search)
   ->setParameter('s', "%".$searchString."%")

MySQL: Select all rows with the same ID, if a column from a joined table contains x

2 Answers2