Mysql select query get data from row based
I have 4 tables:
- Source
- fields (fields for source)
- source_data li>
- source_data_details (child of source_data containing records stored in string technique)
source
id name status
1 web active
field
id source_id name config status
1 1 record_id 101 active
2 1 firstname 101 active
3 1 surname 101 active
source_data
id source_id status
1 1 active
2 1 active
source_data_details
id source_data_id source_field_id value
1 1 1 1avhh2
2 1 2 john
3 1 3 mavrick
4 2 1 87k3jo
5 2 2 peter
6 2 3 lyne
How can I query it to create the result
source_data.id record_id firstname surname
1 1avhh2 john mavrick
2 87k3jo peter lyne
+3
source to share
1 answer
You can use multiple joins or aggregation. Here's an example using aggregation:
select sdd.source_data_id,
max(case when f.name = 'record_id' then sdd.value end) as record_id,
max(case when f.name = 'firstname' then sdd.value end) as firstname,
max(case when f.name = 'surname' then sdd.value end) as surname
from source_data_details sdd join
field f
on sdd.field_id = f.id
group by sdd.source_data_id;
Note that you must explicitly put in every column you want in the output. If you need variable columns you need to use dynamic SQL statements ( prepare
/ execute
).
+1
source to share