How to select from joined MySQL tables?

Question

I basically have two tables:

A(id int, name varchar(10), info varchar(10))
B(id int, item varchar(10))

A
1 A Hello
2 B World
3 C Foo

B
1 apple
1 orange
1 hammer
2 glass
2 cup

Using the following query...

mysql> SELECT a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ')
       FROM a
       LEFT OUTER JOIN b ON (a.id=b.id)
       GROUP BY 1,2,3;

I get the desired output:

+------+------+-------+------------------------------------+
| id   | name | info  | group_concat(b.item separator ' ') |
+------+------+-------+------------------------------------+
|    1 | A    | Hello | apple orange hammer                | 
|    2 | B    | World | glass cup                          | 
|    3 | C    | Foo   | NULL                               | 
+------+------+-------+------------------------------------+

I would now like to modify the query to get a result table containing the same information but only those who have items starting with the letter o, (like 'o%') How do I get the following output?

+------+------+-------+------------------------------------+
| id   | name | info  | group_concat(b.item separator ' ') |
+------+------+-------+------------------------------------+
|    1 | A    | Hello | apple orange hammer                |                        
+------+------+-------+------------------------------------+

Note that I still want to present the whole string 'apple orange hammer', not only 'orange'

Answer 1

SELECT a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ')
FROM a
LEFT OUTER JOIN b ON (a.id=b.id)
WHERE a.id in (select id from b where upper(SUBSTRING(b.item,0,1)) = 'O')
GROUP BY 1,2,3

Answer 2

SELECT * FROM
  (SELECT a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ')
  FROM a
  LEFT OUTER JOIN b ON (a.id=b.id)
  GROUP BY 1,2,3)
WHERE a.id IN (SELECT bb.id FROM b AS bb WHERE name like 'o%');

I doubt this performs well, but it seems logical to me.

Answer 3

After the group by you need to use having to do the like operator on the group_concat.

Group By 1,2,3
HAVING group_concat(b.item SEPARATOR ' ') like ('%o%')

Please note I am just looking for any o you would need to adjust the like statement to match your requirements.

Answer 4

Yet another way to do it:

SELECT a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ') AS items
   FROM a
   LEFT OUTER JOIN b ON (a.id=b.id)
   GROUP BY 1,2,3
   HAVING items REGEXP '[[:<:]]o';

Answer 5

SELECT  a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ') AS bs
FROM    a
LEFT OUTER JOIN
        b
ON      b.id = a.id
GROUP BY
        id, name, info
HAVING   
        bs RLIKE '[[:<:]]o'

or this (more index efficient):

SELECT  a.id, a.name, a.info,
        (
        SELECT  GROUP_CONCAT(b.item SEPARATOR ' ') AS bs
        FROM    b
        WHERE   b.id = a.id
        ) AS bs
FROM    (
        SELECT  DISTINCT b.id
        FROM    b
        WHERE   item LIKE 'o%'
        ) q
JOIN    a
ON      a.id = q.id

The latter query is faster if you have a composite index on b (item, id)

Answer 6

Thank you for the suggestions! Which one do you think performs the best?