Nehil Mistry Nehil Mistry - 6 months ago 13
SQL Question

Mysql Join for null data

I am having 3 tables which are as following:

Table - Milestone
+----+----------+---------------------+---------------------+
| id | name | details | timestamp |
+----+----------+---------------------+---------------------+
| 1 | 1st Year | One year Completed | 2016-05-14 08:13:08 |
+----+----------+---------------------+---------------------+
| 2 | 2nd Year | Two years Completed | 2016-05-14 08:15:08 |
+----+----------+---------------------+---------------------+

Table Update-Type
+----+-----------+
| id | type |
+----+-----------+
| 1 | Milestone |
+----+-----------+
| 2 | Offer |
+----+-----------+

Table - Image
+----+-----------+-------------+-----------------+--------------------+
| id | update_id | update_type | path | timestamp |
+----+-----------+-------------+-----------------+--------------------+
| 1 | 1 | 1 | img/image_1.jpg | 2016-05-1408:13:08 |
+----+-----------+-------------+-----------------+--------------------+
| 2 | 1 | 1 | img/image_2.jpg | 2016-05-1408:14:08 |
+----+-----------+-------------+-----------------+--------------------+


In above Table image - update_id = milestone_id or offer_id & update_type is type of update.

Also condition is, some milestome has image and some doesn't. I want to join 3 tables such that if no image exist show
null
or
group_concat
path of images with similar update_id and update_type.

I've tried following query but it only giving me milestone with images.

SELECT milestone.id, milestone.name, milestone.details,
GROUP_CONCAT(images.path) as path, update_type.id AS update_type
FROM milestone
LEFT OUTER JOIN images ON milestone.id = images.update_id
INNER JOIN update_type ON update_type.id = images.update_type
WHERE images.update_type ='1' AND update_type.type = 'Milestone'
GROUP BY milestone.id


Current Output I am getting :

+---+----------+--------------------+----------------------------------+-------------+
| | name | details | path | update_type |
+---+----------+--------------------+----------------------------------+-------------+
| 1 | 1st Year | One year Completed | img/image_1.jpg, img/image_2.jpg | 1 |
+---+----------+--------------------+----------------------------------+-------------+


Output I should Get:

+----+----------+---------------------+----------------------------------+-------------+
| id | name | details | path | update_type |
+----+----------+---------------------+----------------------------------+-------------+
| 1 | 1st Year | One year Completed | img/image_1.jpg, img/image_2.jpg | 1 |
+----+----------+---------------------+----------------------------------+-------------+
| 2 | 2nd Year | Two years Completed | NULL | 1 |
+----+----------+---------------------+----------------------------------+-------------+

Answer

You can check this here.

SELECT milestone.id, milestone.name, milestone.details,
  GROUP_CONCAT(images.path SEPARATOR ', ') AS path,
  update_type.id AS update_type
FROM milestone
JOIN update_type ON update_type.type = 'Milestone'
LEFT JOIN images ON milestone.id = images.update_id AND update_type.id = images.update_type
GROUP BY milestone.id

Your mistake was in using LEFT JOIN images and WHERE images.update_type ='1'. If you are using condition in where on left joined table, it eliminates LEFT JOIN. LEFT JOIN will act as INNER JOIN - no null rows will be returned.

Second way is to modify WHERE in your query and allow null values for images. Something like this:

WHERE (images.id IS NULL OR images.update_type ='1')