How can I get values null after inner join?

user_5770216 0
Viewed 33

I have this structure:

Table A -- |id_A | field_1a | id_B | value_A|

Table B -- |id_B | field_1b | field_2b | value_B|

Table C -- |field_1c | id_D | id_A|

Table D -- |id_D | description|

I want to get a query which returns:

-- Value A and if this value is null, returns its value in B.

-- Description of Table D through Table C. If row is not in C returns null.

I achieved the first part but I am stuck in the second part. As far as I tried this is my query:

select a.id_A
if (VALOR_b is null, VALOR_a, VALOR_b) as valor,
d.description 
from A a inner join B b 
on a.id_B = b.id_B
 inner join C c
on c.id_A = A.id_A
 inner join D d 
on d.id_D = c.D

As I said I am getting the first part and just the values what I can cross through C, but I need when I can cross them a null value in the previously part of the query.

Hoping I explain clear enough. Please, ask me any doubt. Thanks in advance.

mysql
1 Answers

Use a left join to both C and D.
Also your life will be easier if you use the SQL standard coalesce() instead of if ():

select
    a.id_A,
    coalesce(VALOR_a, VALOR_b) as valor,
    d.description 
from A a
join B b on a.id_B = b.id_B
left join C c on c.id_A = A.id_A
left join D d on d.id_D = c.D

Left join still returns a row from the parent table if there is no matching row, in which case it returns all nulls from the joined table"

coalesce() takes any number of arguments and returns the first non-null value.

user_2561960
Related