Reputation: 1524
Before we begin, don't blame me, I didn't design the database. I am well aware that it is possible to add columns when necessary.
Consider this snippet of mySQL:
SELECT r.id,
crf.field_name,
crf.field_value
FROM reports r
JOIN custom_report_fields crf
ON crf.report_id = r.id
WHERE r.id IN ( 100, 101 )
AND field_name LIKE "service_%"
Which yields these unhelpful results:
ID | field_name | field_value
------------------------------------------------
100 | service_id_1 | 20
100 | service_name_1 | "Shipping"
100 | service_cost_1 | 5.25
101 | service_id_1 | 21
101 | service_name_1 | "Handling"
101 | service_cost_1 | 7.50
101 | service_id_2 | 20
101 | service_name_2 | "Shipping"
101 | service_cost_2 | 7.75
I am in need of a query that will yield these results like so:
Report ID | Field ID | Name | Cost
------------------------------------------------
100 | 20 | "Shipping" | 5.25
101 | 21 | "Handling" | 7.50
101 | 20 | "Shipping" | 7.75
Mind you, there could any number of costs associated with a report, with each successive cost getting another three entries and an increased suffix ('service_id_x', 'service_name_x', 'service_cost_x').
Answerer of this question will receive a far superior afterlife than other followers of your chosen religion.
Upvotes: 2
Views: 74
Reputation: 23135
Try this solution. It's quite clunky(it can only be so elegant given the clunky nature of the design itself!), but it works:
SELECT
a.id AS Report_ID,
MAX(CASE WHEN b.field_name LIKE 'service_id_%' THEN b.field_value END) AS Field_ID,
MAX(CASE WHEN b.field_name LIKE 'service_name_%' THEN b.field_value END) AS Name,
MAX(CASE WHEN b.field_name LIKE 'service_cost_%' THEN b.field_value END) AS Cost
FROM
reports a
INNER JOIN
custom_report_fields b ON a.id = b.report_id
WHERE
a.id IN (100, 101) AND
b.field_name LIKE 'service_%'
GROUP BY
a.id,
SUBSTRING_INDEX(b.field_name, '_', -1)
Upvotes: 2