Example: there are nine listings in the foreign key table. Three with product #10 that are greases; three with product #15 that are gear oils and three with product #10 that are engine oils.
The results should show one #10 grease & engine oil, and one #15 gear oil.
The results show one #10 grease, grease, grease, engine oil, engine oil, engine oil, and #15 gear oil, gear oil, gear oil.
It looks like the product is showing correctly, but not the lubes. I've tried multiple combinations of GROUP BY and Group_Concat, but the lube results are always the same. I have a recordset using GROUP BY on another page working properly (though it SELECTS from a foreign key table, rather than a parent table like the one I'm having issues with. Tried changing the SELECT * FROM prod to FROM prod type (the foreign key table), but with no difference. Query below:
SELECT *, Group_Concat(Lube) AS LubeCats
FROM prod LEFT OUTER JOIN prodtype ON prod.ID = prodtype.ProdIDType LEFT OUTER JOIN lubetype ON prodtype.LubeID = lubetype.LubeTypeID LEFT OUTER JOIN prodsize ON prod.ID = prodsize.ProdSizeID LEFT OUTER JOIN contsize ON prodsize.ContSizeID = contsize.ContID LEFT OUTER JOIN Tech_DS ON prod.ID = Tech_DS.TDS_ID LEFT OUTER JOIN industry ON industry.IndustryID = prodtype.IndustryTypeID
GROUP BY ID
If I need to post this for further troubleshoot, let me know. I'll need to update the Db first.
