How to write SQL to select a value in table B if not exists in table A otherwise select value in tab-CodePudding

I want to get a default rate for activity or an override rate if one exists in another table. How can I write SQL for this?

I have this query but it produces an error "every derived table must have its own alias".

select A.id, rate from (
  select 
    A.id, coalesce(B.rate, A.rate) as rate
  from
    A
  left join B on B.id = A.id
);

Consider the following data

table user
user_id name
1       johnny
2       sam

table activity_types
activity_type_id description  rate
1                cook steak   $12.00
2                flip burgers $9.00
3                wait tables  $8.00
4                wash dishes  $8.00

table personal_override_rates
user_id activity_type_id rate
1       1                $18

table activities
activity_id user_id activity_type_id qty
1           1       1                1
2           1       2                1
3           2       1                1
4           2       2                1

desired result:

johnny cook steak   1 $18.00
johnny flip burgers 1 $9.00
sam    cook steak   1 $12.00
sam    flip burgers 1 $9.00

CodePudding user response：

The error you get is because you have a derived table without an alias. You must provide one and then select the id and rate using the alias.

Like this:

select derived_table.id, derived_table.rate from (
  select 
    A.id, coalesce(B.rate, A.rate) as rate
  from
    A
  left join B on B.id = A.id
) as derived_table;