即使找不到SQL值，也要保留该行

Question

我试图保留一行，即使它缺少值。但我就是想不通。

10.4.14-MariaDB

示例查询：

SELECT
data.name,
am.value AS color
FROM
color_data cd
INNER JOIN color_data am
ON cd.value = am.id
INNER JOIN data
ON cd.id = data.id
WHERE
data.type = "vehicles"
AND data.slug LIKE "%something%"
AND cd.key = "color_id"
AND am.key = "color"

我得到的：

----------------------
name   |  color   |
----------------------
car      | red
bicycle  | blue

我想要什么：

----------------------
name   |  color   |
----------------------
car      | red
boat     |         <-- When this is empty keep the row.
bicycle  | blue

因此，当颜色的ID丢失时，该行将被删除。我想保持这一排。这可能是我所缺少的相当基本的东西。

Answer 1

您需要用从data开始的join重写查询，使用LEFT JOIN而不是INNER JOIN，并将color表上的条件放在联接条件中：

SELECT
data.name,
am.value AS color
FROM
data
LEFT JOIN
color_data cd ON cd.id = data.id AND cd.key = 'color_id'
LEFT JOIN
color_data am ON am.id = cd.value AND am.key = 'color'
WHERE
data.type = 'vehicles'
AND data.slug LIKE '%something%'

Answer 2

从要保存所有行的表开始使用LEFT JOIN：

SELECT data.name, am.value AS color
FROM data
color_data cd LEFT JOIN
ON cd.id = data.id AND
cd.key = 'color_id' LEFT JOIN
color_data am
ON cd.value = am.id AND
am.key = 'color'
WHERE data.type = 'vehicles' AND
data.slug LIKE '%something%';

注：

• data现在是FROM子句中的第一个，因为您希望保留这些行
• data的筛选条件在WHERE子句中
• 其他表的筛选条件在相应的ON子句中
• 单引号是字符串的SQL标准，而不是双引号