Join different tables based on condition

Question

create table [premiumuser] (user_id int, name nvarchar(50));
create table [liteuser] (user_id int, name nvarchar(50));
create table [feature] (id nvarchar(50), user_id int, userkey int);

insert into [premiumuser] select 1, 'stephen';
insert into [premiumuser] select 2, 'roger';

insert into [liteuser] select 1, 'apollo';
insert into [liteuser] select 2, 'venus';

insert into feature select 'Upload content', 1, 1;
insert into feature select 'Create account', 1, 0;
insert into feature select 'View content', 2, 0;

I would like to see data from feature table and instead of userid i want the username.
The catch here is if userkey is 0, get the username from liteuser table, else from premiumuser table.
Data should be like

'Upload content', 'stephen', 1
'Create account', 'apollo', 0
'View content', 'venus', 0

Answer 1

Try this:

select 
    f.id,
        case when userkey=0 then l.name else p.name end as username
from [feature] f
    left join [liteuser] l on l.user_id = f.user_id
    left join [premium user] p on p.user_id = f.user_id

Answer 2

SELECT f.id
, (CASE WHEN f.userkey = 0 THEN l.name ELSE p.name END) AS name
, f.userkey
FROM feature f
LEFT JOIN liteuser l on f.user_id = l.user_id
LEFT JOIN premiumuser p on p.user_id = l.user_id

I suggest using left joins over inner joins, as you seem to be asking about all users associated with a given feature. This way, features with no associated users will have NULL as the name. Additionally, using two inner joins against the user tables will only return only those features which have an entry for the same user in both the premium and lite tables.