SQL - Crossing tables within tables

Question

SQL - Crossing tables within tables

Navigation

#1 by (1 votes)

1

I do not know if the title is appropriate, but come on.

I have in Table A (table of processes queried) two key fields (new_process and old_process) and I have in my table B my interest variable (new_process_process). In Table C, I have the conversion from the old process to the new one. Exemplifying:

Table A
pcs_novo   pcs_antigo   outras_A 
A          NULL         X
B          NULL         X
NULL       AA           X
NULL       BB           X
C          NULL         X
A          NULL         X
NULL       AA           X

Table B
pcs_novo   assunto  
A          1           
B          2       
C          3
D          4
E          5
F          6

Table C
pcs_antigo  pcs_novo
AA          D
BB          E
CC          F

Of course, I want to:

Final Table
    pcs_novo  pcs_antigo   assunto   outras_A
    A         NULL         1         X
    B         NULL         2         X
    D         AA           4         X
    E         BB           5         X
    C         NULL         3         X

How do I get this?

Notes: In Table A, the process fields repeat. In Table B and C the fields are unique.

sql sql-server

asked by anonymous 03.07.2017 / 22:15

1 answer

Change MenuItem Popup Template WPF Send Base64 Image to Email

score 1 · Accepted Answer

Let's start by typing the tables A and C , so we'll definitely have a pcs_novo valid:

SELECT
    coalesce(A.pcs_novo, C.pcs_novo) as pcs_novo,
    A.pcs_antigo,
    A.outros_a
FROM A 
    LEFT JOIN C ON (A.pcs_antigo = C.pcs_antigo)

The coalesce will ensure that if I do not get a non-null value of A.pcs_novo , it will get C.pcs_novo .

The LEFT JOIN will ensure that every possible join of A with C will be done, and all lines of A will be displayed, even those that do not have matching in C

We can get this result and join it with the B table to get the subject:

SELECT
    coalesce(A.pcs_novo, C.pcs_novo) as pcs_novo,
    A.pcs_antigo,
    B.assunto,
    A.outros_a
FROM A 
    LEFT JOIN C ON (A.pcs_antigo = C.pcs_antigo)
    LEFT JOIN B ON (coalesce(A.pcs_novo, C.pcs_novo) = B.pcs_novo)

Since rows in A can have pcs_novo or pcs_antigo repeated, to get only the unique values for the processes, we can use distinct to not get repetitions:

SELECT DISTINCT
    coalesce(A.pcs_novo, C.pcs_novo) as pcs_novo,
    A.pcs_antigo,
    B.assunto,
    A.outros_a
FROM A 
    LEFT JOIN C ON (A.pcs_antigo = C.pcs_antigo)
    LEFT JOIN B ON (coalesce(A.pcs_novo, C.pcs_novo) = B.pcs_novo)