Elias MP Elias MP - 3 years ago 186
MySQL Question

How can I get values null after inner join?

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download