How to get non-existing rows in many to many relationship

Question

I am having following three tables

products

------------------
|  id | name     |
------------------
|  1  | Ariel    |
|  2  | Milk     |
------------------

price_list

-----------------------
|  id | name          |
-----------------------
|  1  | Trade Price   |
|  2  | Net Price     |
|  3  | Retail Price  |
-----------------------

product_prices

(it has only two records for product 'Ariel')

----------------------------------------------
|  id | product_id | price_list_id  | price  |
----------------------------------------------
|  1  |     1      |       1        |   100  |
|  2  |     1      |       2        |   110  |
----------------------------------------------

Desired Result:

------------------------------------------------------------
|  id | product_name |                prices               |
------------------------------------------------------------
|     |              |  | id | price_list_name | price |   |
|  1  |     Ariel    |  --------------------------------   |
|     |              |  | 1  |  Trade Price    |  100  |   |
|     |              |  | 2  |  Net Price      |  110  |   |
|     |              |  | 3  |  Retail Price   |  null |   |
|     |              |  --------------------------------   |
|     |              |  | id | price_list_name | price |   |
|  2  |     Milk     |  --------------------------------   |
|     |              |  | 1  |  Trade Price    |  null |   |
|     |              |  | 2  |  Net Price      |  null |   |
|     |              |  | 3  |  Retail Price   |  null |   |
                        --------------------------------   |
------------------------------------------------------------

I tried following query to get a cross between products and price_list

SELECT p.id, 
       p.NAME, 
       pl.id, 
       pl.NAME 
FROM   products p 
       CROSS JOIN price_list pl 
WHERE  pl.id NOT IN (SELECT product_id 
                     FROM   product_prices)

Any idea how to achieve the desired result?

Answer 1

I think something like this. I changed the names of the fields and tables for short. I also display all the fields.

select * from t1 cross join t2 left join t3 on t1.id = t3.t1_id and t2.id = t3.t2_id order by t1.id, t2.id;

+----+-------+----+--------------+------+-------+-------+-------+
| id | name  | id | name         | id   | t1_id | t2_id | price |
+----+-------+----+--------------+------+-------+-------+-------+
|  1 | Ariel |  1 | Trade Price  |    1 |     1 |     1 |   100 |
|  1 | Ariel |  2 | Net Price    |    2 |     1 |     2 |   110 |
|  1 | Ariel |  3 | Retail Price | NULL |  NULL |  NULL |  NULL |
|  2 | Milk  |  1 | Trade Price  | NULL |  NULL |  NULL |  NULL |
|  2 | Milk  |  2 | Net Price    | NULL |  NULL |  NULL |  NULL |
|  2 | Milk  |  3 | Retail Price | NULL |  NULL |  NULL |  NULL |
+----+-------+----+--------------+------+-------+-------+-------+

• products -> t1
• price_list -> t2
• product_prices -> t3