Question: I have the following problem:  

Given 3 tables:  

 A with columns id( bigint ), price( integer )  

 B with columns id( bigint ), bedrooms( integer )  

 C with columns id( bigint ), bedrooms( integer )  

I have to add to the table A the bedrooms joining the "id" column from the tables B,C respectively in the following way:  

First look for bedrooms in B based on the "id". If the "id" is not there, look in the table C.
If the id is not found in B & C the record in A is excluded.

How would I get the asked result?

```
create table A(
    id BIGINT,
    price INTEGER
);
create table B(
    id BIGINT,
    bedrooms INTEGER
);
create table C(
    id BIGINT,
    bedrooms INTEGER
);

insert into A(id,price)
    values
        (1,1000),
        (2,1200),
        (3,1600),
        (4,2100),
        (5,1200);

insert into B(id,bedrooms)
    values
        (1,1),
        (2,5),
        (4,2),
        (6,2);

insert into C(id,bedrooms)
    values
        (1,2),
        (3,4),
        (4,3);

select A.id,A.price,
    (case 
        when A.id = B.id then B.bedrooms
        when A.id = C.id then C.bedrooms
    end) as bedrooms
from A 
inner join B on A.id = B.id
inner join C on A.id = C.id
order by A.id

```

Expected Output:

[![Expected Output](https://i.stack.imgur.com/KY8Rg.png)](https://i.stack.imgur.com/KY8Rg.png)

Answer: 
We can do `LEFT JOIN` on `B` and `C` tables separately using `id`. Now, we can utilize [`COALESCE()`](https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_coalesce), to consider `B.bedrooms` first (if not null), else `C.bedrooms`. Now, there is a chance that both can be null (that is, `id` does not exist in either of the tables). 

In that case, if you don't want those rows, you can put an additional `WHERE` condition to consider only those id(s), which has atleast one matching row in either `B` or `C`.

Here is one way:

```
select A.id,
       A.price,
       COALESCE(B.bedrooms, C.bedrooms) AS bedrooms 
from A 
left join B on A.id = B.id
left join C on A.id = C.id
WHERE NOT (B.bedrooms IS NULL AND 
           C.bedrooms IS NULL)
order by A.id

```
Try this way. Using left join will only take the values which are there.

```
select A.id,A.price,
    (case 
        when B.id is not null then B.bedrooms
        when ((B.id is null) and (C.id is not null)) then C.bedrooms
    end) as bedrooms
from A 
left join B on A.id = B.id
left join C on A.id = C.id
order by A.id

```