Inner join with multiple tables

I have these four tables:

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

And also:

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)

All the products can have a different color and/or material. E.g. I can have a product that has one or more material options but no colors associated and vice versa. The output should be something like this:

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

I have tried the following statement but it returns 0 rows:

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;

Any ideas?

Answers


You could do something like this:

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

I would advise you reviewing JOINs. There is a great visual explanation of joins online that will help you write these queries.


Well, you need to learn how to build joins, and the way I normally do it is by selecting one table and join the next one and the next one and the next one until I have the result I want.

select product_id, product_name
  from products

next I join the first one I need so I go ahead and say

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

On the join it is important to figure out if I maybe have nothing to join with and I still want to see the line. So I rather use a left join

     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)

That way you add each table to join. By the way. Is this homework?


If you just need IDs, keep it simple

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

Need Your Help

Combining multiple commits before pushing in Git

git git-squash

I have a bunch of commits on my local repository which are thematically similar. I'd like to combine them into a single commit before pushing up to a remote. How do I do it? I think rebase does thi...

Spring Boot - Error creating bean with name 'dataSource' defined in class path resource

java spring spring-boot

I have Spring Boot web application. It's centered around RESTful approach. All configuration seems in place but for some reason MainController fails to handle request. It results in 404 error. How ...