CODEWITHSUNDEEP
mysqlsqlconcatenation
Jorg Ancrath
Jorg Ancrath

Reputation: 1447

GROUP_CONCAT on two tables

I have two tables, with independent ids (can't be connected via joins), I want to query and get a GROUP_CONCAT of both columns.

Example: table "a" has ids: 1, 2, 3. table "b" has the ids: 10, 11.

End result should be: 1, 2, 3, 10, 11

I have tried a few queries:

SELECT CONCAT_WS(',', GROUP_CONCAT(a.id), GROUP_CONCAT(b.id)) AS combined FROM a, b

SELECT GROUP_CONCAT(a.id, b.id) AS combined FROM a, b

These queries are returning me duplicate results though 8as in, all results from a twice and all results from b twice as well)

Upvotes: 1

Views: 2362

Answers (4)

Mark Bolusmjak
Mark Bolusmjak

Reputation: 24409

If you want duplicates, use union all. If you don't want duplicates, use union. In either case, the query you need is as follows:

select group_concat(id) from
 (select id from a
  union
  select id from b) as ids;

Upvotes: 0

Hasholef
Hasholef

Reputation: 753

The following query will generate that you want. You can play with the table_position dynamic column for deciding which table goes first.

 Select group_concat(id order by table_position) from 
   (
      select id, 1 as table_position from a
      union all
      select id, 2 as table_position from b
    )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Try union all:

select group_concat(ab.id) as ids
from ((select id from a
      ) union all
      (select id from b
      )
     ) ab;

Your queries are doing cross join's between the tables, so data after the cross join is:

a.id        b.id
 1           10
 1           11
 2           10
 2           11
 3           10
 3           11

After the union all, the data is:

ab.id
  1
  2
  3
 10
 11

Upvotes: 2

cyadvert
cyadvert

Reputation: 875

GROUP_CONCAT(DISTINCT [])

will help

https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Upvotes: 0

Related Questions