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.

Answer 1

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