MySQL group_concat returning values (including NULL) from two tables

Question

I have two tables. I'm trying to return the thumbnail_img and fullsize_img column respective values for each article_steps row. I want this to include NULL if there are no matching values for each step, too.

CREATE TABLE IF NOT EXISTS `article_steps` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `article_id` int(10) NOT NULL,
  `step_num` int(3) NOT NULL,
  `step_title` char(100) NOT NULL,
  `step_body` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `step_body` (`step_body`,`step_title`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

CREATE TABLE IF NOT EXISTS `article_steps_gallery` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `article_id` int(5) NOT NULL,
  `step_num` int(5) NOT NULL,
  `thumbnail_img` text NOT NULL,
  `fullsize_img` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

Here's my statement:

SELECT a.id,a.step_num, a.step_title,a.step_body, CONCAT( (case when a.step_num=s.step_num then GROUP_CONCAT(fullsize_img, '|',thumbnail_img SEPARATOR '    ') end) ) AS images
FROM article_steps a, article_steps_gallery s 
WHERE a.article_id=s.article_id 
AND a.article_id=2
GROUP BY step_num

This is returning:

id  step_num    step_title  step_body   images  
5   1           faketitle1  fakebody1   NULL
6   2           faketitle2  fakebody2   image.jpg|image_thumbnail.jpg
7   3           faketitle3  fakebody3   NULL
8   4           faketitle4  fakebody4   NULL

The only step_num which should be returning NULL is #1 --- however it's only correctly returning fullsize_img & thumbnail_img for step_num 2.

---

SELECT a.step_num, a.step_title,a.step_body, GROUP_CONCAT(DISTINCT fullsize_img) AS image, GROUP_CONCAT(DISTINCT thumbnail_img) AS thumbnail
FROM article_steps a, article_steps_gallery s 
WHERE a.article_id=s.article_id 
AND a.step_num=s.step_num 
AND a.article_id=2
GROUP BY step_num

This statement here returns the correct images. But, being that it's GROUP_CONCAT it doesn't return NULL values. So the step_num 1, which doesn't have any images doesn't get returned.

id  step_num    step_title  step_body   image       thumbnail
6   2           faketitle2  fakebody2   image2.jpg  image2_thumbnail.jpg
7   3           faketitle3  fakebody3   image3.jpg  image3_thumbnail.jpg
8   4           faketitle4  fakebody4   image4.jpg  image4_thumbnail.jpg

Answer 1

I got it to work with the following code:

SELECT a.id,a.step_num, a.step_title,a.step_body,
(SELECT GROUP_CONCAT(fullsize_img) FROM article_steps_gallery s  WHERE a.step_num=s.step_num AND a.article_id=s.article_id) AS image,
(SELECT GROUP_CONCAT(thumbnail_img) FROM article_steps_gallery s  WHERE a.step_num=s.step_num AND a.article_id=s.article_id) AS thumbnail
FROM article_steps a
WHERE a.article_id=1