CODEWITHSUNDEEP
sqlsql-server-2008join
Null Head
Null Head

Reputation: 2941

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

Upvotes: 14

Views: 37986

Answers (3)

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

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

Upvotes: 29

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

You need to know about JOINS and CASE condition.

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

EDIT :

As suggested by Pieter Geerkens , a LEFT JOIN may be required by you. LEFT JOIN results in retrieving matching rows from both tables PLUS non matching rows from table situated on leftside of join . The rows from rightside table are filled with NULL value.

INNER JOIN only gives rows having matching value in joining field.

Upvotes: 3

Esoteric Screen Name
Esoteric Screen Name

Reputation: 6112

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.

Upvotes: 7

Related Questions