Geek asks and answers

Join different tables based on condition

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 the data from the function table , but instead useridI want username.
The trap here, if userkeyequal to 0, get the table usernamefrom liteuser , otherwise from the table the premium user .
Data should be similar to

'Upload content', 'stephen', 1
'Create account', 'apollo', 0
'View content', 'venus', 0
+5
source share
3 answers

Try the following:

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
+14
source 
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

, , , , . , NULL . , , - Lite.

+3

JOINS CASE .

SELECT f.id , CASE userkey WHEN 0 then l.name ELSE p.name END,f.userkey
FROM feature f
INNER JOIN liteuser
     ON f.user_id=l.user_id
INNER JOIN premiumuser p
     ON f.user_id=p.user_id

:

, LEFT JOIN. LEFT JOIN . , . NULL.

INNER JOIN , .

+2

More articles:


All Articles