title: Inner join with multiple tables
body: <p>I have these four tables:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And also:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>-----------------------------------------------------------------------------
  | 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        |
  -----------------------------------------------------------------------------
  </code></pre>
  
  <p>I have tried the following statement but it returns 0 rows:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Any ideas?</p>