I’ve got 2 tables in my database the first table is
leader
JavaScript
x
id | name | age
1 | John | 40
2 | Doe | 35
member
JavaScript
id | name | age
1 | Mark | 40
2 | Jessica| 35
2 | Tiff | 31
And what I want is like this
JavaScript
Group 1
Leader : John 40
Member : Mark 40
Group 2
Leader : Doe 35
Member : Jessica 35
Tiff 31
My query
JavaScript
SELECT *
FROM (
SELECT id, name, age
FROM leader
UNION
SELECT id, name, age,
FROM member
) x
ORDER BY id
I’ve tried using UNION and JOIN but it’s not working. Can someone help me, and form of help will very much appreciated. Thanks everyone!
Advertisement
Answer
You can use UNION ALL
like this:
JavaScript
select t.col
from (
select 0 type, id, concat('Group ', id) col
from leader
union all
select 1, id, concat('Leader: ', name, ' ', age)
from leader
union all
select 2, id, concat('Member: ', name, ' ', age)
from member
) t
order by t.id, t.type
Results:
JavaScript
> | col |
> | :----------------- |
> | Group 1 |
> | Leader: John 40 |
> | Member: Mark 40 |
> | Group 2 |
> | Leader: Doe 35 |
> | Member: Jessica 35 |
> | Member: Tiff 31 |
Or:
JavaScript
select t.col
from (
select 0 type, id, concat('Group ', id) col
from leader
union all
select 1, id, concat('Leader: ', name, ' ', age)
from leader
union all
select 2, id, concat('Member: ', group_concat(concat(name, ' ', age) separator 'n '))
from member
group by id
) t
order by t.id, t.type
Results:
JavaScript
> | col |
> | :------------------------------------ |
> | Group 1 |
> | Leader: John 40 |
> | Member: Mark 40 |
> | Group 2 |
> | Leader: Doe 35 |
> | Member: Jessica 35 |
> | Tiff 31 |
See the demo.