我有三个表,如下所示:
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 |
+----+-----------+-------------+-----------------+--------------------+
在上表中,image-update_id=milestone_id或offer_id&update_type是更新的类型。
同样的情况是,有些里程计有图像,有些没有。我想加入3个表,以便如果不存在图像,则显示具有类似update_id和update_type的图像的null
或group_concat
路径。
我尝试过以下查询,但它只给了我一个带有图像的里程碑。
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
我得到的电流输出:
+---+----------+--------------------+----------------------------------+-------------+
| | name | details | path | update_type |
+---+----------+--------------------+----------------------------------+-------------+
| 1 | 1st Year | One year Completed | img/image_1.jpg, img/image_2.jpg | 1 |
+---+----------+--------------------+----------------------------------+-------------+
我应该得到的输出:
+----+----------+---------------------+----------------------------------+-------------+
| 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 |
+----+----------+---------------------+----------------------------------+-------------+
您可以在此处进行检查。
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
您的错误在于使用LEFT JOIN images
和WHERE images.update_type ='1'
。若在左侧联接表的where中使用条件,则会消除LEFT JOIN
。LEFT JOIN
将充当INNER JOIN
——不会返回空行。
第二种方法是修改查询中的WHERE
,并允许图像为null值。类似这样的东西:
WHERE (images.id IS NULL OR images.update_type ='1')