Внутреннее соединение с несколькими таблицами

У меня эти четыре таблицы:

PRODUCTS
---------
PRODUCT_ID
PRODUCT_TITLE
(other fields)

COLORS
---------
COLOR_ID
COLOR_NAME

MATERIALS
---------
MATERIAL_ID
MATERIAL_NAME

IMAGES
---------
IMAGE_ID
BIG
MED
SMALL
THUMB

SIZE
---------
SIZE_ID
SIZE_NAME

А также:

PRODUCT_COLOR
---------
PRODUCT_ID
COLOR_ID

PRODUCT_MATERIAL
---------
PRODUCT_ID
MATERIAL_ID

PRODUCT_SIZE
---------
PRODUCT_ID
SIZE_ID

PRODUCT_IMAGE
---------
PRODUCT_ID
IMAGE_ID
COLOR_ID (can be null)
MATERIAL_ID (can be null)

Все продукты могут иметь другой цвет и/или материал. Например. У меня может быть продукт, который имеет один или несколько вариантов материала, но не содержит цветов и наоборот. Результат должен быть примерно таким:

-----------------------------------------------------------------------------
| PRODUCT_ID | PRODUCT_NAME  | COLOR_ID | MATERIAL_ID | IMAGE_ID | SIZE_ID |
-----------------------------------------------------------------------------
| 1          | T-SHIRT       | 1        | null        | 1        | 1        |
| 1          | T-SHIRT       | 1        | null        | 1        | 2        |
| 1          | T-SHIRT       | 1        | null        | 1        | 3        |
| 1          | T-SHIRT       | 1        | null        | 1        | 4        |
| 2          | JEANS         | null     | 1           | 2        | 1        |
| 2          | JEANS         | null     | 1           | 2        | 2        |
| 2          | JEANS         | null     | 1           | 2        | 3        |
| 2          | JEANS         | null     | 1           | 2        | 4        |
| 2          | JEANS         | null     | 1           | 2        | 5        |
| 3          | T-SHIRT VNECK | 2        | 2           | 3        | 1        |
| 3          | T-SHIRT VNECK | 2        | 2           | 3        | 2        |
| 3          | T-SHIRT VNECK | 3        | 2           | 4        | 1        |
| 3          | T-SHIRT VNECK | 3        | 2           | 4        | 2        |
| 3          | T-SHIRT VNECK | 4        | 3           | 5        | 1        |
| 3          | T-SHIRT VNECK | 4        | 3           | 5        | 2        |
-----------------------------------------------------------------------------

Я пробовал следующий оператор, но он возвращает 0 строк:

SELECT PRODUCTS.PRODUCT_ID, PRODUCTS.PRODUCT_TITLE, COLORS.COLOR_ID, MATERIALS.MATERIAL_ID, IMAGES.IMAGE_ID, SIZE.SIZE_ID from PRODUCTS
    INNER JOIN PRODUCT_COLOR ON (PRODUCTS.PRODUCT_ID = PRODUCT_COLOR.PRODUCT_ID)
    INNER JOIN COLORS ON (COLORS.COLOR_ID = PRODUCT_COLOR.COLOR_ID)
    INNER JOIN PRODUCT_MATERIAL ON (PRODUCTS.PRODUCT_ID = PRODUCT_MATERIAL.PRODUCT_ID)
    INNER JOIN MATERIALS ON (MATERIALS.MATERIAL_ID = PRODUCT_MATERIAL.MATERIAL_ID)
    INNER JOIN PRODUCT_IMAGE ON (PRODUCTS.PRODUCT_ID = PRODUCT_IMAGE.PRODUCT_ID)
    INNER JOIN IMAGES ON (IMAGES.IMAGE_ID = PRODUCT_IMAGE.IMAGE_ID)
    INNER JOIN PRODUCT_SIZE ON (PRODUCTS.PRODUCT_ID = PRODUCT_SIZE.PRODUCT_ID)
    INNER JOIN SIZE ON (SIZE.SIZE_ID = PRODUCT_SIZE.SIZE_ID)
    ORDER BY PRODUCTS.id_PRODUCT;

Любые идеи?

Ответы

Ответ 1

Вы можете сделать что-то вроде этого:

select p.product_id,
  p.product_name,
  c.color_id,
  m.material_id,
  i.image_id,
  s.size_id
from products p
left join product_color pc
  on p.product_id = pc.product_id
left join colors c
  on pc.color_id = c.colorid
left join product_material pm
  on p.product_id = pm.product_id
left join materials m
  on pm.material_id = m.material_id
left join product_image pi
  on p.product_id = pi.product_id
left join images i
  on pi.image_id = i.image_id
  or c.color_id = i.color_id
  or m.material_id = i.material_id
left join product_size ps
  on p.product_id = ps.product_id
left join size s
  on ps.size_id = s.size_id

Я бы посоветовал вам рассмотреть JOIN s. Существует большое визуальное объяснение подключений в Интернете, которое поможет вам написать эти запросы.

Ответ 2

Ну, вам нужно научиться строить соединения, и, как я обычно делаю это, выбирая одну таблицу и присоединяйте следующую, а следующую и следующую, пока я не получу результат, который я хочу.

select product_id, product_name
  from products

next Я присоединяюсь к первому, который мне нужен, поэтому я иду вперед и говорю

select p.product_id, p.product_name, pc.color_id
  from products p
  join product_color pc on (pc.product_id = p.product_id)

В соединении важно выяснить, могу ли я не с чем поучаствовать, и я все еще хочу видеть эту строку. Поэтому я предпочитаю использовать левое соединение

     select p.product_id, p.product_name, pc.color_id
       from products p
  left join product_color pc on (pc.product_id = p.product_id)

Таким образом вы добавляете каждую таблицу для присоединения. Кстати. Это домашнее задание?

Ответ 3

Если вам просто нужны идентификаторы, пропустите

select p.product_id,
  p.product_name,
  pc.color_id,
  pm.material_id,
  pi.image_id,
  ps.size_id
from products p, 
PRODUCT_COLOR pc, 
product_material pm, 
PRODUCT_SIZE ps, 
PRODUCT_IMAGE pi
where 
p.product_id = pc.product_id(+)
and p.product_id = pm.product_id(+)
and p.product_id = ps.product_id(+)
and p.product_id = pi.product_id(+);