SQL - Crossing tables within tables

Question

I don’t know if the title is appropriate, but come on.

I have in Table A (table of consulted processes) two key fields (new process and old process) and I have in Table B my variable of interest (new process). In Table C, I have the conversion of the old process to the new one. Illustrating:

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

Naturally, 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 it?

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

Answer 1

Let’s start connecting the tables A and C, so we will 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 assure me that in case I don’t get a non-zero value of A.pcs_novo, he’s gonna get from C.pcs_novo.

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

We can take this result and join with the table B to take up 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)

Like the lines in A may have pcs_novo or pcs_antigo repeated, to take only the unique values for the processes, we can use the distinct not to catch the 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)