Dom Dom - 1 month ago 9
MySQL Question

MySQL query to select rows matching criteria and rows related to the matching rows

I would like to select all rows where a column matches a criteria, but also select all rows that don't match the criteria, but have a relation to the rows that do match the criteria.

Given a table structure like this:

group_id | word
---------+------
1 | the
2 | cat
2 | sat
3 | on
1 | the
3 | mat


Given the criteria
WHERE word LIKE '%at%'
, I'd want to get the matching rows

2 | cat
2 | sat
3 | mat


but I also want to get the related rows. That is, rows with a group_id equalling the group_id of any row matching the criteria, which in this case would be group_id 2 or 3. The final result should be:

2 | cat
2 | sat
3 | on
3 | mat


I think that a self join is the way to go, but I can't quite figure it out.

Answer

One method uses in:

select t.*
from t
where t.group_id in (select t2.group_id
                     from t t2
                     where t2.word LIKE '%at%'
                    );

If you try to do the same thing using join, you might get duplicate results.