I have a very long query that I have shortened and anonymized so the problem becomes clearer.
Here is an approximation of my query:
SELECT subquery1.productNumber, subquery1.anotherinfo, subquery2.label_a, subquery2.label_b, subquery2.label_c FROM (SELECT productNumber, anotherinfo FROM table1 WHERE somethingelse = 'limitation') as subquery1 LEFT JOIN (SELECT productNumber, CASE WHEN table2.label = 'H' THEN 'example1' WHEN table2.label = 'I' THEN 'example2' END AS 'label_a', CASE WHEN table2.label = 'J' THEN 'example3' WHEN table2.label = 'K' THEN 'example4' WHEN table2.label = 'L' THEN 'example5' END AS 'label_b', CASE WHEN table3.label = 'M' THEN 'example6' END AS 'label_c' FROM table2 WHERE label IN ('H', 'I', 'J', 'K', 'L', 'M')) AS subquery2 ON subquery2.productNumber = subquery1.productNumber ORDER BY subquery1.productNumber ASC
I'd want to have one row for each "ProductNumber" with the information for the labels in the same record. I cannot use
GROUP BY instead of
ORDER BY because, as soon as I use it, label_b gets recorded earliest in table2, so that information is the only one displayed from that subquery, and the result changes to the following:
More subqueries are not a preferred option as the entire thing is already way too big. It should be possible to fuse it via Spreadsheet/Excel, but maybe there is a simple Mysql solution for it I just have not thought about yet.
Does anyone have an idea on how to solve it?