MySQL即使连接数据为空也如何返回行 [英] Mysql how to return rows even if join data is empty
问题描述
我有三个表(product
,product_description
,product_store_description
).
I have three tables (product
, product_description
, product_store_description
).
产品
此表有一行
+----------+---------------------+
|product_id| description |
+----------+---------------------+
| 1 |'regular description'|
+----------+---------------------+
产品说明
此表有零行
+----------+-----------+--------+
|product_id|description|language|
+----------+-----------+--------+
product_store_description
This table has zero rows
+----------+-----------+--------+--------+
|product_id|description|language|store_id|
+----------+-----------+--------+--------+
我想为所有表WHERE product.product_id = '1'
返回.description
列.
I would like to return the .description
column for all tables WHERE product.product_id = '1'
.
所以在这种情况下,我想要的输出将是
So in this case the output I would like would be
$row = $result->fetch_assoc();
echo '<pre>';
var_dump($row);
echo '</pre>';
array (size=3)
'p_description' => string 'regular description' (length=19)
'pd_description' => string '' (length=0)
'psd_description' => string '' (length=0)
我已经尝试了许多SQL变体,这就是目前的样子.
I have tried many variations of my SQL and this is how it stands at the moment.
SELECT p.description AS p_description ,pd.description AS pd_description, psd.description AS psd_description FROM product AS p
RIGHT JOIN product_description AS pd ON(pd.product_id = p.product_id)
RIGHT JOIN product_store_description AS psd ON(psd.product_id = p.product_id)
WHERE p.product_id = '1'
AND pd.language = 'en'
AND psd.language = 'en' AND psd.store_id = 1;
这将返回零行
我搜索了一个导致我使用RIGHT JOIN
的答案,但这并不能解决我的问题.
I have searched for an answer which has led me to use a RIGHT JOIN
but this has not solved my problem.
我已经创建了示例表的 HERE 的SQL小提琴,但是我无法让任何人都可以上班.
Ive created a SQL fiddle HERE of the example tables, unfortunately I haven't been able to get anyone answer to work yet.
推荐答案
如果表product
包含至少一个数据.并且您想要显示其他列NULL
或自定义字符串(例如"---"
),即使连接属性为null,也必须在查询中指定要连接的列.因此,以下查询将为您提供数据.
If table product
is containing atleast one data. And You want to show other columns either NULL
or custom string like "---"
the you have to specify in query that join even if joining attribute is null. So following query will give you data.
SELECT
p.description AS p_description ,ifnull(pd.description,"---") AS pd_description,
ifnull(psd.description,"--") AS psd_description
FROM product AS p
LEFT JOIN Product_description AS pd ON(pd.product_id = p.product_id)
LEFT JOIN product_store_description AS psd ON(psd.product_id = p.product_id)
WHERE p.product_id = '1' AND (pd.language = 'en' or pd.language is null)
AND (psd.language = 'en' or psd.language is null) AND
(psd.store_id = 1 or psd.store_id is null);
这篇关于MySQL即使连接数据为空也如何返回行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!