CODEWITHSUNDEEP
mysql
Dom
Dom

Reputation: 3080

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.

Upvotes: 2

Views: 462

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can join to a derived table that returns all selected group_id values:

SELECT t1.*
FROM mytable AS t1
JOIN (SELECT DISTINCT group_id
      FROM mytable
      WHERE word LIKE '%at%'
) AS t2 ON t1.group_id = t2.group_id

You have to use DISTINCT in the subquery, so as to be sure that you get one row per group_id, so that the final result doesn't contain any duplicates.

Upvotes: 1

Dom
Dom

Reputation: 3080

I think I've figured out how to do it with a self join.

SELECT DISTINCT `t1`.* 
FROM `test` AS `t1` JOIN `test` AS `t2` 
ON `t1`.`group_id`=`t2`.`group_id`  
WHERE `t2`.`text` LIKE '%at%'

I don't know if there is a better (more efficient) way to do this query.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 2

Related Questions