SQL GROUP_CONCAT query assistance

I am having some problems figuring out the best use of GROUP_CONCAT. In the query below I am trying to select multiple images from JAM_Plot_Images and set them against single records. So get record 1, display image1.jpg, image2.jpg in each row. Then get record 2, display image3.jpg, image4.jpg in the 2nd row and so on.

SELECT *, GROUP_CONCAT(JAM_Plot_Images.image ORDER BY JAM_Plot_Images.image) AS images
FROM JAM_Plots
LEFT JOIN JAM_Plot_Images ON JAM_Plots.page_id = JAM_Plot_Images.page_id 
GROUP BY JAM_Plots.page_id

The problem I have is if a row has no images in a row it breaks the unique identifier when outputting the records, but only for that record. So if records 1 2 and 4 have images it will output everything fine, but if record 3 has no image the unique ID won’t appear. NULL values appear within phpmyadmin output. I have tried to use COALESCE to fix the issue but can’t quite get it to work.

Answer

This problem has nothing to do with GROUP_CONCAT(), it’s a general problem with LEFT JOIN when you have duplicate column names between tables.

Since you have the same column name page_id in both tables, both of them will be selected in the results. But when there’s no images, JAM_Plot_Images.page_id will be NULL.

To disambiguate them, you should give an alias to JAM_Plots.page_id, and use that.

SELECT *, JAM_Plots.page_id AS jp_page_id, GROUP_CONCAT(...)
...