I have a table which is named: candidate_info has three columns are id_user, id_type and value:
to store user information:
I consider two queries as below, it will combine from rows to columns:
Form Original:
Id User | Id Type | Value
1 | 1 | John
1 | 2 | [email protected]
1 | 3 | Ho Chi Minh
1 | 4 | 123
2 | 1 | Ana
2 | 2 | [email protected]
2 | 3 | New York
2 | 4 | 456
To New:
Id User | Fullname | Email | Mailing_Address | Phone Number
1 | John | [email protected] | Ho Chi Minh | 123
2 | Ana | [email protected] | New York | 456
1. First query:
select
c1.id_user,
c1.value as fullname,
c2.value as email,
c3.value as mailing_address,
c4.value as phone_number
from
candidate_info c1
left join
candidate_info c2 ON c1.id_user = c2.id_user
left join
candidate_info c3 ON c1.id_user = c3.id_user
left join
candidate_info c4 ON c1.id_user = c4.id_user
where
c1.id_type = 1
and c2.id_type = 2
and c3.id_type = 3
and c4.id_type = 4;
2. Second query
select
c.id_user,
MAX(IF(c.id_type = 1, c.value, NULL)) as fullname,
MAX(IF(c.id_type = 2, c.value, NULL)) as email,
MAX(IF(c.id_type = 3, c.value, NULL)) as mailing_address,
MAX(IF(c.id_type = 4, c.value, NULL)) as phone_number
from
candidate_info c
where
c.id_type in (1, 2, 3, 4)
group by c.id_user
Which is better?
Edit: Change id_entry_field to id_type, join to left join to make sense.
The second query is better because it doesn't create a large cartesian product as the first query does. Instead, it iterates over the table's records only once, emitting a row containing aggregate data for each group.