CODEWITHSUNDEEP
phpmysqljoin
Nehil Mistry
Nehil Mistry

Reputation: 1109

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

Upvotes: 1

Views: 40

Answers (1)

Andrew
Andrew

Reputation: 1866

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

Upvotes: 1

Related Questions