Mysql how to return rows even if join data is empty

Question

I have three tables (product, product_description, product_store_description).

product

This table has one row

+----------+---------------------+
|product_id|      description    |
+----------+---------------------+
|    1     |'regular description'|
+----------+---------------------+

product_description

This table has zero rows

+----------+-----------+--------+
|product_id|description|language|
+----------+-----------+--------+

product_store_description

This table has zero rows

+----------+-----------+--------+--------+
|product_id|description|language|store_id|
+----------+-----------+--------+--------+

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)

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;

This returns zero rows

I have searched for an answer which has led me to use a RIGHT JOIN but this has not solved my problem.

Ive created a SQL fiddle HERE of the example tables, unfortunately I haven't been able to get anyone answer to work yet.

descriptions fields must have same value ? (table's schema is very crappy and redundant) — MTroy, Jan 14 '16 at 11:57
@MTroy: Looks like the description in the second table is language dependant, in the third table it could be localised further according to the store. You should not make value judgements on the design without understanding the application. — PaulF, Jan 14 '16 at 12:00

score 5 · Accepted Answer · answered Jan 14 '16 at 12:37

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

score 1 · Answer 2 · edited Jan 14 '16 at 12:01

1

If I understand you right, then you need LEFT join because the product table is on the left side of the join in your query.

Left join will return all rows from the table on the left side of the join operator, while right join will return all rows from the table on the right hand side of the join operator.

edited Jan 14 '16 at 12:01

Shadow

33,525
10
51
64

answered Jan 14 '16 at 11:57

PaulF

6,673
2
18
29

Thanks but I started with a left join, this returns 0 rows. – TarranJones Jan 14 '16 at 12:27
try moving the 2nd/3rd table conditions up to the join : _SELECT p.description AS p_description ,pd.description AS pd_description, psd.description AS psd_description FROM product AS p LEFTJOIN product_description AS pd ON(pd.product_id = p.product_id) AND pd.language = 'en' LEFT JOIN product_store_description AS psd ON(psd.product_id = p.product_id) AND psd.language = 'en' AND psd.store_id = 1 WHERE p.product_id = '1';_ – PaulF Jan 14 '16 at 12:36

score -3 · Answer 3 · answered Jan 14 '16 at 12:03

-3

If you want to receive some data you should execute query like this:

SELECT 
   p.description AS p_description ,
   pd.description AS pd_description, 
   psd.description AS psd_description,
   pd.language as language,
   psd.store_id as store_id
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'

But in this case you manually had to control values for language and store_id

answered Jan 14 '16 at 12:03

KLin

479
2
10

this would return descriptions for any store or any langauge – TarranJones Jan 14 '16 at 12:31
@TarranJones thats why i wrote "But in this case you manually had to control values for language and store_id" – KLin Jan 14 '16 at 12:50

Mysql how to return rows even if join data is empty

3 Answers3

Linked

Related