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 approach.

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

```