CODEWITHSUNDEEP
mysqlsqlif-statementjoincase
Michael
Michael

Reputation: 111

Join with CASE or other alternative (MySQL)

I have 2 tables that I need to join. Table 1:

User |Country|
--------------
 a   |  NZ   |
 a   |  NZ   |
 a   |  HU   |
 a   |  IL   |
 a   |  AU   |
 a   |  AU   |
 a   |  RO   |
 a   |  NZ   |
 a   |  NZ   | 
 a   |  NZ   |
 a   |  GB   |
 a   |  GB   |

Table 2:

   User  |Country| Payment
    ----------------------
     a   |   GB  |  20
     a   |   AU  |  20
     a   |       |  30

The final result that I want to get is this one:

User | Country | Payment
--------------
 a   |   NZ    |   30
 a   |   NZ    |   30
 a   |   HU    |   30
 a   |   IL    |   30
 a   |   AU    |   20
 a   |   AU    |   20
 a   |   RO    |   30
 a   |   NZ    |   30
 a   |   NZ    |   30
 a   |   NZ    |   30
 a   |   GB    |   20
 a   |   GB    |   20

In the result there are 2 conditions:

1 - If the country is equal in both tables then give me the corresponding Payment, i.e. only for GB and AU in this case.

2 - If the country in table 2 is NULL then join the payment to all other countries (except GB and AU)

This thing can be done with 2 left joins, but is there a way to do it with 1? Something like CASE/IF. I looked at several examples joining with CASE, but it's not working for me.

Thanks.

Upvotes: 0

Views: 475

Answers (3)

Michael
Michael

Reputation: 111

My solution was this one:

LEFT JOIN Countries b ON INSTR(a.geo, b.Code) <> 0

So the JOIN is possible only when the instance is matching.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

While I would imagine 2 outer joins would be more efficient, since you only want to use a single join, here's one option using a subquery and conditional aggregation:

select usr, country, coalesce(payment, defaultpayment) as payment
from (
    select t1.usr, t1.country, 
        max(case when t1.country = t2.country then payment end) payment,
        max(case when t2.country is null then payment end) defaultpayment
    from t1
       left join t2 on t1.usr = t2.usr 
    group by t1.usr, t1.country 
) t

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269913

This is where you want "defaulting". A left join is handy, in fact two. The first matches to the country exactly; the second brings in the NULL value.

select t1.*, coalesce(t2.payment, t2d.payment) as payment
from t1 left join
     t2 t2
     on t1.country = t2.country left join
     t2 t2d
     on t2d.country is null;

This is the best way to solve the problem, because it can make use of an index on t2(country).

There are other ways; for instance:

select t1.*
       (select t2.payment
        from t2
        where t2.country = t.country or t2.country is null
        order by (t2.country is not null) desc
       ) as payment
from t;

Or, you could use one explicit join and aggregation. Or something cumbersome like this:

select t1.*, t2.payment
from t1 join
     t2
     on t1.country = t2.country
union all
select t1.*, t2.payment
from t1 join
     t2
     on t2.country is null
where t.country not in (select t2.country from t2 where t2.country is not null);

But the version with two left joins is the best approach.

Upvotes: 1

Related Questions