CODEWITHSUNDEEP
mysqlsql
HooMin.Lee
HooMin.Lee

Reputation: 147

Multiple rows to one column

table    A        B
column  key      name
         1       John
         1       Michael
         2       Alice
         2       Josh

On my database when i do this query:

SELECT A.key, GROUP_CONCAT(B.name SEPARATOR ', ') 
  FROM A 
  LEFT JOIN B ON A.key = B.key 
 WHERE B.name = 'John'
 GROUP BY A.key

My Output:

  key      name
  1        John

Expected Output:

  key      name
  1        John, Michael

Upvotes: 1

Views: 381

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65138

If you need the result containing the name John, then the following query with exists keyword might be used :

SELECT A.key, GROUP_CONCAT(B.name ORDER BY B.name SEPARATOR ', ') as "Names"
  FROM A 
  LEFT JOIN B ON A.key = B.key 
 WHERE EXISTS ( SELECT 1 FROM B WHERE name = 'John' AND B.key = A.key )
 GROUP BY A.key;

Demo

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query

select A.`key`, GROUP_CONCAT(B.'name' SEPARATOR ', ') 
from (select `key` from tblB where n='John' limit 1) as A 
inner join tblB B on B.`key` = A.`key`
group by A.`key`

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

You could add a HAVING clause to your query which asserts that a given set of key records contains the name John:

SELECT
    a.`key`,
    GROUP_CONCAT(b.name) names
FROM A a
LEFT JOIN B b
    ON a.`key` = b.`key`
GROUP BY
    a.`key`
HAVING
    SUM(b.name = 'John') > 0;

Note: key is a reserved MySQL keyword, and you should not use it to name your columns (though maybe you only gave it above as an example).

Upvotes: 1

Related Questions