Mysql Join for null data

Question

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           |
+----+----------+---------------------+----------------------------------+-------------+

Output shows result only with images. I want all milestone such that if there's no entry exist in image it should show path as null — Nehil Mistry
– Nehil Mistry, Commented May 14, 2016 at 14:05
You are selecting non aggregate columns in your query. Please fix this. — Tim Biegeleisen
– Tim Biegeleisen, Commented May 14, 2016 at 14:21

Andrew · Accepted Answer · 2016-05-14 14:29:24Z

1

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')

answered May 14, 2016 at 14:29

Andrew

1,86615 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql Join for null data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related