I am currently retrieving data from multiple tables using a cus_id as the initial query. From here I have multiple tables that can have various rows of data that I would like to retrieve and use GROUP_CONCAT to return this data in a single row. At the moment, my query is returning duplicate data based on the max number of rows returned by one of the group concats.
SELECT a.id,c.x,c.y,c.z
GROUP_CONCAT(a.column_a) AS aca,
GROUP_CONCAT(a.column_b) AS acb,
GROUP_CONCAT(b.column_a) AS bca,
GROUP_CONCAT(b.column_b) AS bcb,
FROM `table_a` a
INNER JOIN `table_b` b ON a.id = b.id
INNER JOIN `table_c` c ON a.id = c.id
WHERE a.id = ?
Also, in this scenario, what is the correct join method to use. I am expecting all the fields I am requesting to have some sort of data.
Problem was resolved by using sub queries to isolate the GROUP_CONCAT requests. This allowed me to get only the data I wanted without duplicate results manipulated by other JOIN requests.
SELECT a.id,c.x,c.y,c.z
(SELECT GROUP_CONCAT(column_a) FROM table_a) AS aca,
(SELECT GROUP_CONCAT(column_b) FROM table_a) AS acb,
(SELECT GROUP_CONCAT(column_a) FROM table_b) AS bca,
(SELECT GROUP_CONCAT(column_b) FROM table_b) AS bcb,
FROM table_a a
INNER JOIN `table_c` c ON a.id = c.id
WHERE a.id = ?