alias some columns names as one field in oracle's join select query
We are developing something like a social networking website. I've got task to do 'follow me' fun开发者_JAVA技巧ctionality. In our website objects are users, teams, companies, channels and groups (please don't ask why there are groups and teams - it is complicated for me too, but teams are releated to user's talent)
Users, teams, channels, companies and groups have all their own tables.
I have a query which gets me all the follower's leaders like this
select
--fo.leader_id,
--fo.leader_type,
us.name as user_name,
co.name as company_name,
ch.title as channel_name,
gr.name as group_name,
tt.name as team_name
from
follow_up fo
left join users us
on (fo.leader_id = us.id and fo.leader_type = 'user')
left join companies co
on (fo.leader_id = co.user_id and fo.leader_type = 'company')
left join channels ch
on (fo.leader_id = ch.id and fo.leader_type = 'channel')
left join groups gr
on (fo.leader_id = gr.id and fo.leader_type = 'group')
left join talent_teams tt
on (fo.leader_id = tt.id and fo.leader_type = 'team')
where
follower_id = 83
I need to get all fields like:
- user_name,
- company_name,
- channel_name,
- group_name,
- team_name
as one field in SELECT's product. I have tried to alias them all the same 'name' but Oracle numbered it. Please help :)
I'm not sure why you need to get them as one field, because aren't you going to need to split the information out on the client side? Anyway, one way you could do it would be like this:
user_name || '|' || company_name || '|' || channel_name || '|' || group_name || '|' || team_name all_fields
This would give you a pipe delimited field called all_fields. If you have multiple user_name fields from different tables, you could use the same approach:
table1.user_name || '|' || table2.user_name ... all_user_names
You could then split the field on the client side.
Personally, I would just do something like this:
table1.user_name table1_user_name
, table2.user_name table2_user_name
...
In other words, just use a unique column alias for each user_name.
Column names in a query result set must be unique. Perhaps you want one row for each user, company, channel, group and team for the given follower? In which case I'd use a query like this:
select fo.leader_type, us.name
from follow_up fo
join users us
on (fo.leader_id = us.id and fo.leader_type = 'user')
where follower_id = 83
UNION ALL
select fo.leader_type, co.name
from follow_up fo
join companies co
on (fo.leader_id = co.user_id and fo.leader_type = 'company')
where follower_id = 83
UNION ALL
select fo.leader_type, ch.title as name
from follow_up fo
join channels ch
on (fo.leader_id = ch.id and fo.leader_type = 'channel')
where follower_id = 83
UNION ALL
select fo.leader_type, gr.name
from follow_up fo
join groups gr
on (fo.leader_id = gr.id and fo.leader_type = 'group')
where follower_id = 83
UNION ALL
select fo.leader_type, tt.name
from follow_up fo
join talent_teams tt
on (fo.leader_id = tt.id and fo.leader_type = 'team')
where follower_id = 83
I got thinking and I come up with this solution:
Is it slower than solution of Jeffrey Kemp?
select
fo.leader_id,
fo.leader_type,
case
when us.subdomain is not null then us.subdomain
when us2.subdomain is not null then us2.subdomain
--when co.name is not null then co.name
when ch.service_url is not null then ch.service_url
when gr.id is not null then to_char(gr.id)
when tt.subdomain is not null then tt.subdomain
else 'nothing!'
end
as leader_url,
case
when us.name is not null then us.name
when co.name is not null then co.name
when ch.title is not null then ch.title
when gr.name is not null then gr.name
when tt.name is not null then tt.name
else 'nothing!'
end
as leader_names,
case
when us.img_avatar_path is not null then us.img_avatar_path
when us2.img_avatar_path is not null then us2.img_avatar_path
--when us.img_avatar_path is not null and fo.leader_id = co.user_id and fo.leader_type = 'company' then us.img_avatar_path
when ch.default_img is not null then ch.default_img
when gr.img_avatar_path is not null then gr.img_avatar_path
when tt.img_avatar_path is not null then tt.img_avatar_path
else 'nothing!'
end
as img_avatar_path,
case
when us.img_avatar_x is not null then us.img_avatar_x
when us2.img_avatar_x is not null then us2.img_avatar_x
when ch.default_img_x is not null then ch.default_img_x
when gr.img_avatar_x is not null then gr.img_avatar_x
when tt.img_avatar_x is not null then tt.img_avatar_x
else 0
end
as img_avatar_x,
case
when us.img_avatar_y is not null then us.img_avatar_y
when us2.img_avatar_y is not null then us2.img_avatar_y
when ch.default_img_y is not null then ch.default_img_y
when gr.img_avatar_y is not null then gr.img_avatar_y
when tt.img_avatar_y is not null then tt.img_avatar_y
else 0
end
as img_avatar_y
from
follow_up fo
left join users us
on (fo.leader_id = us.id and fo.leader_type = 'user')
left join companies co
on (fo.leader_id = co.user_id and fo.leader_type = 'company')
left join users us2
on (co.user_id = us2.id)
left join channels ch
on (fo.leader_id = ch.id and fo.leader_type = 'channel')
left join groups gr
on (fo.leader_id = gr.id and fo.leader_type = 'group')
left join talent_teams tt
on (fo.leader_id = tt.id and fo.leader_type = 'team')
where
follower_id = :follower_id
精彩评论