How to join 4 tables in SQL that is returning empty ? (Inner, Outer or left Join )

Question

I have 4 tables with the following relationships:

Products has N Variations

Sizes has N Variations

Colors has N Variations

Variations belongs to Products,Sizes and Colors

The structure of the tables is like this:

Variation

     id | product_id | size_id | color_id |  quantity |    barcode    
    -------------+---------+---------+------+----------------------+
      1 |    1       |    1    |     1    |    10     |   100991001 



Product

     id |    cod    | description | price       
    -------------+---------+---------+------
      1 |    160    |    T-Shirt  |  10.00



Color

     id |    cod    | description      
    -------------+---------+-----
      1 |    10     |    Blue    



Size

         id |    size     
        -------------+
          1 |    P     

I need the following result:

Result
   cod_produto  |   desc_produto    |   desc_cor    |   desc_tamanho    |   preco   | quantidade    | barcode
       160      |      T-Shirt      |    Blue       |       P           |   10.00   |     10        | 100991001

But my query (below) is returning empty.

   SELECT   product.cod as cod_produto,
            product.description as desc_produto,
            color.description as desc_cor,
            size.size as desc_tamanho,
            product.price as preco,
            variation.quantity as quantidade,
            variation.barcode as cod_barras
    FROM    variation INNER JOIN
            product ON product.id = variation.product_id
            INNER JOIN size ON size.id = variation.size_id
            INNER JOIN color ON color.id = variation.color_id

What is the best way to do this query and what would be the correct syntax for My SQL and Postgres ?

Answer 1

Your query returns exactly what you are asking in the question.

--Variation

--     id | product_id | size_id | color_id |  quantity |    barcode    
--    -------------+---------+---------+------+----------------------+
--      1 |    1       |    1    |     1    |    10     |   100991001 

declare @Variation table(id int, product_id int, size_id int, color_id int, quantity int,    barcode int)
insert into @Variation values (1 ,   1      ,   1   ,     1   ,    10    ,   100991001 )

--Product

--     id |    cod    | description | price       
--    -------------+---------+---------+------
--      1 |    160    |    T-Shirt  |  10.00

declare @Product table(id int, cod int, description varchar(20), price numeric(18,2))
insert into @Product values (1,160,'T-Shirt',10.00)

--Color

--     id |    cod    | description      
--    -------------+---------+-----
--      1 |    10     |    Blue    

declare @Color table(id int, cod int, description varchar(20))
insert into @Color values (1,10,'Blue')

--Size

--         id |    size     
--        -------------+
--          1 |    P     

declare @Size table(id int, size char(1))
insert into @Size values  (1,'P')


SELECT      product.cod as cod_produto,
            product.description as desc_produto,
            color.description as desc_cor,
            size.size as desc_tamanho,
            product.price as preco,
            variation.quantity as quantidade,
            variation.barcode as cod_barras
    FROM    @Variation  variation
            INNER JOIN @Product product ON product.id = variation.product_id
            INNER JOIN @Size size ON size.id = variation.size_id
            INNER JOIN @Color color ON color.id = variation.color_id