JoinS About the same Table with Null Values

Question

JoinS About the same Table with Null Values

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

You are trying to perform two JOINS to match the information of two tables, the first table contains a catalog of articles.

id_art      desc      
------------------------
A1          Articulo A1
A2          Articulo A2
A3          Articulo A3
A4          Articulo A4
A5          Articulo A5

The second table contains reorder points for most items by branch.

id          id_art      max     pro     min
----------------------------------------------
01          A1          0       2       5 
02          A1          1       2       4
02          A2          1       2       6
01          A3          1       3       5
02          A3          2       4       6
01          A5          0       2       3

What I need is for the result of the Query to give me the following

id      suc         max     pro     min     suc         max     pro     min
---------------------------------------------------------------------------
A1      01          0       2       5       02          1       2       4
A2      NULL        NULL    NULL    NULL    02          1       2       6
A3      01          1       3       5       02          2       4       6
A5      01          0       2       3       NULL        NULL    NULL    NULL

Currently the Query that I generated only results in articles that have a record in branch 01 and 02, omitting individual records

id      suc         max     pro     min     suc         max     pro     min
---------------------------------------------------------------------------
A1      01          0       2       5       02          1       2       4
A3      01          1       3       5       02          2       4       6

I'm trying to organize it with the following Query:

SELECT 
a.id_art,
r1.suc,
r1.max,
r1.pro,
r1.min,
r2.suc,
r2.max,
r2.pro,
r2.min 
FROM articulos a
RIGHT JOIN(SELECT suc,max,pro,min FROM MMR
    WHERE suc='01'
) r1 ON r1.id_art=a.id_art
RIGHT JOIN(SELECT suc,max,pro,min FROM MMR
    WHERE suc='02'
) r2 ON r2.id_art=a.id_art
ORDER BY a.id_art

I appreciate your support, I'm a bit lost since it's the first time I've worked with SQL, thanks.

sql query join

asked by Emmanuel LC 11.05.2017 в 21:08

source

2 answers

Get the name of an object in C # Normalize database up to the Third Normal Form

score 1 · Answer 1

The problem is that you are using a RIGHT JOIN where you should be using a LEFT JOIN :

SELECT 
a.id_art,
r1.suc,
r1.max,
r1.pro,
r1.min,
r2.suc,
r2.max,
r2.pro,
r2.min 
FROM articulos a
LEFT JOIN(SELECT suc,max,pro,min FROM MMR
    WHERE suc='01'
) r1 ON r1.id_art=a.id_art
LEFT JOIN(SELECT suc,max,pro,min FROM MMR
    WHERE suc='02'
) r2 ON r2.id_art=a.id_art
ORDER BY a.id_art

Personally, I would modify the way of doing the join to avoid using unnecessarily derived tables:

SELECT 
a.id_art,
r1.suc,
r1.max,
r1.pro,
r1.min,
r2.suc,
r2.max,
r2.pro,
r2.min 
FROM articulos a
LEFT JOIN MMR r1
  ON r1.id_art=a.id_art
 AND r1.suc='01'
LEFT JOIN MMR r2
  ON r2.id_art=a.id_art
 AND r2.suc='02'
ORDER BY a.id_art

Another way to write the query that prevents you from doing 2 joins is as follows:

select a.id_art,
       case(when m.suc = '01' then m.suc end) as suc01,
       case(when m.suc = '01' then m.max end) as max01,
       case(when m.suc = '01' then m.pro end) as pro01,
       case(when m.suc = '01' then m.min end) as min01,
       case(when m.suc = '02' then m.suc end) as suc02,
       case(when m.suc = '02' then m.max end) as max02,
       case(when m.suc = '02' then m.pro end) as pro02,
       case(when m.suc = '02' then m.min end) as min02
  from articulos a
  left join mmr m
    on m.id_art = a.id_art
 group by a.id_art
 order by a.id_art

score 1 · Answer 2

In the case that 01 and 02 are fixed.

SELECT 
    Art.id_art,
    ArtSuc.suc,
    ArtSuc.min,
    ArtSuc.pro,
    ArtSuc.max,
    ArtSuc2.suc,
    ArtSuc2.min,
    ArtSuc2.pro,
    ArtSuc2.max
FROM 
    articulos Art 
    LEFT JOIN (SELECT suc,max,pro,min FROM MMR) ArtSuc ON 
        Art.id_art = ArtSuc.id_art AND ArtSuc.id = '01'
    LEFT JOIN (SELECT suc,max,pro,min FROM MMR) ArtSuc2 ON 
        Art.id_art = ArtSuc2.id_art AND ArtSuc2.id = '02'
WHERE
    ArtSuc.id IS NOT null OR ArtSuc2.id IS NOT NULL