Как оформить такой sql-запрос?

Question

[ince]

Есть две таблицы:

main:
_____________________
|  PROD   |  LIST_ID  |
------------------------  
|       a     |      3        |
------------------------
|       a     |      4        |
------------------------
|       b     |      1        |
------------------------

list:
_____________________
|   ID   |   LIST_VAL  |
------------------------
|   1    |       11         |
------------------------
|   2    |       22         |
------------------------
|   3    |       33         |
------------------------
|   4    |       44         |
------------------------

Нужно взять все строки из list и соединить их с каждой строкой из main (по полям main.LIST_ID и list.ID) и получить следующее:

_________________________________
|  PROD   |  LIST_ID  | LIST_VAL  |
--------------------------------------  
|      a      |      1        |      null      |
--------------------------------------
|      a      |      2        |      null      |
--------------------------------------
|      a      |      3        |       33       |
--------------------------------------
|      a      |      4        |       44       |
---------------------------------------------
|      b      |      1        |       11       |
--------------------------------------
|      b      |      2        |      null      |
--------------------------------------
|      b      |      3        |      null      |
--------------------------------------
|      b      |      4        |      null      |
--------------------------------------

Answer 1

[idShura]

select l.prod, l.id list_id, l.list_val from list l left join main m on <Дальше сам>

Comments

[ince]

нет, здесь нет null'ов

Answer 2

[Rsa97]

SELECT `p`.`PROD` AS `PROD`, `l`.`ID` AS `LIST_ID`,
       IF(`m`.`PROD` IS NULL, NULL, `l`.`LIST_VAL`) AS `LIST_VAL`
  FROM (
    SELECT DISTINCT `PROD`
      FROM `main`
  ) AS `p`
  JOIN `list` AS `l`
  LEFT JOIN `main` AS `m`
    ON `m`.`PROD` = `p`.`PROD` AND `m`.`LIST_ID` = `l`.`ID`
  ORDER BY `p`.`PROD`, `l`.`ID`