Q:

MySQL join return null if table doesn't contain value

I have three tables:
product
+----+-----------------------------------------------------+
| id | title                                               |
+----+-----------------------------------------------------+
|  1 | Lorem Ipsum is simply dummy text of the printing: 1 |
|  2 | Lorem Ipsum is simply dummy text of the printing: 2 |
|  3 | Lorem Ipsum is simply dummy text of the printing: 3 |
|  4 | Lorem Ipsum is simply dummy text of the printing: 4 |
|  5 | Lorem Ipsum is simply dummy text of the printing: 5 |
+----+-----------------------------------------------------+

currency:
+----+-------+-------+
| id | alias | title |
+----+-------+-------+
|  1 | JPY   | JPY   |
|  2 | USD   | USD   |
|  3 | GBP   | GBP   |
+----+-------+-------+

product_price:
+------------+-------------+-------------+
| product_id | currency_id | price       |
+------------+-------------+-------------+
|          1 |           1 | 3600000.000 |
|          1 |           2 |     160.000 |
|          2 |           1 | 3260000.000 |
+------------+-------------+-------------+

I want to get price of special product, it's easy when using inner join if the table product_price contain the price of the product. But I want to have the result if the table product_price doesn't contain price of the product.
For example:
On the site, user is selecting "JPY", so I can show the product 1 with price is 3600000.000.
But when user switch to "GBP", It can't show the product 1 because it doesn't has price.
I'm using both left join, left outer join and cross join but I can't get the right result.
My expect result when user choose "GBP" for product 1 is:
+----+-----------------------------------------------------+-------------+----------------+-------+
| id | title                                               | currency_id | currency_alias | price |
+----+-----------------------------------------------------+--------------------------------------|
|  1 | Lorem Ipsum is simply dummy text of the printing: 1 |           3 | GBP            | NULL  |
+----+-----------------------------------------------------+-------------+----------------+-------+

Please help me, thank you!

A:

You will have to generate all possible combinations between products and currencies, using CROSS JOIN. Then you can use these combinations as a Derived Table, and do a LEFT JOIN to the junction table:
SELECT
  dt.*, pp.price
FROM 
(
  SELECT
    p.id AS product_id, 
    p.title AS product_tile, 
    c.id AS currency_id, 
    c.alias AS currency_alias, 
    c.title AS currency_title 
  FROM product AS p
  CROSS JOIN currency AS c
) AS dt 
LEFT JOIN product_price AS pp 
  ON pp.product_id = dt.product_id AND 
     pp.currency_id = dt.currency_id 
WHERE dt.currency_title = 'GBP'  -- You can provide currency filter here

