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

Question

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

Navigation

#1 by (1 votes)

1

I have 4 tables with the following relationships:

Products have N Variations

Sizes have N Variations

Colors have N Variations

Variations belongs to Products, Sizes and Colors

The structure of the tables looks 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?

mysql sql ruby-on-rails postgresql rails4

asked by anonymous 02.06.2016 / 19:21

1 answer

Because "my program" did not increment the value 5 being that it less than 5.5 How to use the bootstrap for websites with a larger layout?

score 1 · Accepted Answer

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