Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to Concatenate the output of multiple Queries output based on a specific column?

I have multiple queries in the following format:

Query 1:

Select id,first_name,middle_name
from table1
where id < 4

Output 1:

id first_name middle_name
1 Linda Marie
2 Mary Alice
3 John Steven

Query 2:

Select id,last_name
from table2
where id < 4

Output 2:
Contains optional column (Last Name), which means it doesn’t show for all entries, only if it has a value.

id last_name
1 Jackson
3 Thomson

How do I get the following?
The ID column the common and correctly associate the different outputs.

id first_name middle_name last_name
1 Linda Marie Jackson
2 Mary Alice NULL
3 John Steven Thomson

More complicated situation

Table1

id Entry Result
1 first_name Linda
2 first_name Mary
3 first_name John
1 second_name Liam
2 second_name Violet
3 second_name Charlotte

Table2

id Entry Result
1 middle_name Marie
2 middle_name Alice
3 middle_name Steven
1 last_name Jackson
3 last_name Thomson

Desired output is still

id first_name middle_name last_name
1 Linda Marie Jackson
2 Mary Alice NULL
3 John Steven Thomson
Tags:

3

Answers


  1. 0

    Just LEFT JOIN to table2.

    select a.id, a.first_name, a.middle_name, b.last_name
from table1 a
left join table2 b
  on a.id = b.id
where a.id < 4

    UPDATE

    Based on your recent edit to the question, you can use conditional aggregation to achieve those updated results.

    select 
 a.id, 
 max(case when a.entry = 'first_name' then a.result end) as first_name,
 max(case when b.entry = 'middle_name' then b.result end) as middle_name, 
 max(case when b.entry = 'last_name' then b.result end) as last_name
from table1 a
left join table2 b
  on a.id = b.id
group by a.id;
    id first_name middle_name last_name
    1 Linda Marie Jackson
    2 Mary Alice
    3 John Steven Thomson

    View on DB Fiddle

    Login or Signup to reply.
  2. 0

    This can be done using LEFT JOIN :

    Select t1.id, t1.first_name, t1.middle_name, t2.last_name
from table1 t1
left join table2 t2 on t1.id = t2.id
where t1.id < 4
    Login or Signup to reply.
  3. 0

    Why would one design tables in a situation of ‘complicated situation’

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search