如何根据从其他子查询中选择的列运行不同的查询 [英] How to run different query based on column selected from other sub query
问题描述
我有3个桌子sec_user,admin,老师。
首先我要匹配用户名和密码从sec_user输入如果匹配则会从sec_user中选择role_id。
如果role_id为1则选择* from admin else如果role_id为2则选择*来自老师。
我尝试过:
SELECT
*
来自
(
SELECT
CASE
当ROLE_ID = 1那么(SELECT * FROM admin)
WHEN ROLE_ID = 2那么(SELECT *来自老师)
END
FROM(从sec_users中选择role_id,其中USER_CODE =:PI_USER_ID和ENCRYPTED_PWD =:PI_PASSWORD) ROLE_ID
)
此查询只有在我选择*
你可以使用简单的连接
选择
...
case role = 1
然后 admin.name
else teacher.name
end as [name],
isnull(admin .name,teacher.name) as [name]
来自 [ security ]
left join admin > ... 和 角色 = 1
left join teacher on ... 和 角色 = 2
其中一个连接将失败,另一个将带来您需要的数据...
i am having 3 tables sec_user , admin, teacher.
first i have to match userid and password enter from sec_user if it matches then role_id will be selected from sec_user.
if role_id is 1 then select * from admin else if role_id is 2 then select * from teacher.
What I have tried:
SELECT
*
FROM
(
SELECT
CASE
WHEN ROLE_ID=1 THEN (SELECT * FROM admin)
WHEN ROLE_ID=2 THEN (SELECT * FROM teacher)
END
FROM (select role_id from sec_users where USER_CODE=:PI_USER_ID and ENCRYPTED_PWD=:PI_PASSWORD) ROLE_ID
)
this query work only if i select single coulumn insteat of *
You can use simple join
select ... case when role = 1 then admin.name else teacher.name end as [name], isnull(admin.name, teacher.name) as [name] from [security] left join admin on ... and role = 1 left join teacher on ... and role = 2
One of the joins will fail and the other will bring the data you need...
这篇关于如何根据从其他子查询中选择的列运行不同的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!