如何从多个表动态选择合适的tableName来创建连接查询 [英] how to choose proper tableName dynamically from multiple tables for creating join query
问题描述
您好，
我想动态创建连接查询，其中包含以下表格。（表格包含大量数据，假设有100条记录）
表1） Usertable：其中包含userid，userfirstname，userlastname
表2） grouptable：其中包含groupid，groupname，groupdescription
表3） usergroup：（包含usertable和grouptable之间的关系）
表4） groupRoles：其中包含groupid和roleid之间的关系（任意随机列）
案例1：
但是如果我想找到Groupid存在/共同在哪个表中给出这样的结果输入图片描述：
TableName | ColumnName
------------- | ------------------
1 ）GroupRoles | Groupid
2）UserGroup | Groupid
查询：
我有1个表单，只接受用户ID。通过使用userid我们必须找到：Userid，Userfirstname，Userlastname和groupname
问题：
但问题是在为join子句创建动态查询时
与案例1一样，有两个表选项：grouproles或usergroup
那么我将如何动态地知道从这两个选项中选择哪个表，以便通过关系表将我带到grouptable，这是我案例中的用户组表
在我的情况下，我必须找到Userid，Userfirstname，Userlastname和groupname
我可以获得Userid，来自usertable的Userfirstname和Userlastname
但是，要获取grouptable中存在的groupname列，我们必须获取关联的usergroup表以获取连接查询。
那么我们如何动态地知道从tableNames（如case1）中选择哪个表，以便我们可以得到G来自查询的roupName列
谢谢。
你好朋友请查看下面的脚本，我创建了临时表基于您的描述，并使用该表结构我还添加了选择查询，从表中选择您想要的字段，您需要将其修改为您的实际数据结构..
我已经在联盟中排除了GROUP ROLES，如果你需要根据它选择任何角色，那么你也可以在选择中添加它...
< pre lang =SQL> DECLARE @ UserTable AS 表（
USERID INT ，
USERFIRSTNAME VARCHAR （ 10 ），
USERLASTNAME VARCHAR （< span class =code-digit> 10 ）
）
DECLARE @ GROUPTABLE AS TABLE （
GROUPID INT ，
GROUPNAME VARCHAR （ 10 ），
GROUPDESCRIPTION VARCHAR （ 20 ）
）
DECLARE @ USERGROUP AS TABLE （
USERID INT ，
GROUPID INT
）
DECLARE @GROUPROLES AS 表（
GROUPID INT ，
ROLEID INT
）
INSERT INTO @ UserTable VALUES （ 1 ，' ABC1'，' XYZ1'）
INSERT INTO @ UserTable VALUES （ 2 ，' ABC2'，' XYZ2'）
INSERT INTO @ UserTable VALUES （ 3 ，' ABC3'， XYZ3'）
INSERT INTO @ GROUPTABLE VALUES （ 1 ，' GROUP1'， GROUP1 DESC'）
INSERT INTO @ GROUPTABLE VALUES （ 2 ，' GROUP2'，' GROUP2 DESC'）
INSERT < span class =code-keyword> INTO @ USERGROUP VALUES （ 1 ， 1 ）
INSERT INTO @ USERGROUP VALUES （ 2 ， 2 ）
INSERT INTO @ USERGROUP VALUES （ 3 ， 1 ）
INSERT INTO @ GROUPROLES VALUES （ 1 ， 1 ）
INSERT INTO @ GROUPROLES VALUES （ 2 ，<跨度class =code-digit> 2 ）
SELECT UserTable.USERID，UserTable .USERFIRSTNAME，UserTable.USERLASTNAME，GroupTable.GROUPNAME
FROM @ UserTable AS UserTable
INNER JOIN @USERGROUP AS UserGroupTable ON UserGroupTable.USERID = UserTable.USERID
INNER JOIN @ GROUPTABLE < span class =code-keyword> AS GroupTable ON GroupTable.GROUPID = UserGroupTable.GROUPID
- INNER JOIN @GROUPROLES AS GroupRoles ON GroupTable.GROUPID = GroupRoles.GROUPID
WHERE UserTable.USERID = 1
输出是这样的...
- 将userid传递给1
USERID USERFIRSTNAME USERLASTNAME GROUPNAME
1 ABC1 XYZ1 GROUP1
- 通过传递userid as 3
USERID USERFIRSTNAME USERLASTNAME GROUPNAME
3 ABC3 XYZ3 GROUP1
- - 将userid传递为2
USERID USERFIRSTNAME USERLASTNAME GROUPNAME
2 ABC2 XYZ2 GROUP2
Hello,
I want to create join query dynamically which contains following tables.(Tables contains large amount of data suppose 100 records)
Table 1)Usertable: Which contains userid, userfirstname, userlastname
Table 2)grouptable: Which contains groupid, groupname, groupdescription
Table 3)usergroup : (contains relation between usertable and grouptable)
Table 4)groupRoles: which contains relation between groupid and roleid(any random column)
Case1:
But If I want to find Groupid is present/common in which tables it gives result like this enter image description here:
TableName | ColumnName
-------------|------------------
1) GroupRoles | Groupid
2) UserGroup |Groupid
Query:
I have 1 form which accepts only userid. By using userid we have To find: Userid,Userfirstname, Userlastname and groupname
Problem:
But the problem is while creating dynamic query for join clause
As in case 1 there are 2 options of tables either grouproles or usergroup
So how I will come to know which table to select from these 2 options dynamically so that it takes me to the grouptable via relational table which is usergroup table in my case
As in my case I have to find Userid,Userfirstname, Userlastname and groupname
I can get the Userid,Userfirstname and Userlastname from usertable
But,to get the groupname column which is present in grouptable we have to take usergroup table for relation to get the join query.
So how we can come to know which table to select from either of tableNames (as in case1 ) dynamically, so that we can get the GroupName column from query
Thank you.
hello friend please review below script, i have created temporary table based on your description, and using that table structure i also added select query which is select your desired fields from tables, you need to modified this to your actual data structure..
I have excluded GROUP ROLES in join, if you need to select any role based on that, then you can add that too in selection..
DECLARE @UserTable AS TABLE ( USERID INT, USERFIRSTNAME VARCHAR(10), USERLASTNAME VARCHAR(10) ) DECLARE @GROUPTABLE AS TABLE ( GROUPID INT, GROUPNAME VARCHAR(10), GROUPDESCRIPTION VARCHAR(20) ) DECLARE @USERGROUP AS TABLE ( USERID INT, GROUPID INT ) DECLARE @GROUPROLES AS TABLE ( GROUPID INT, ROLEID INT ) INSERT INTO @UserTable VALUES(1,'ABC1','XYZ1') INSERT INTO @UserTable VALUES(2,'ABC2','XYZ2') INSERT INTO @UserTable VALUES(3,'ABC3','XYZ3') INSERT INTO @GROUPTABLE VALUES(1,'GROUP1','GROUP1 DESC') INSERT INTO @GROUPTABLE VALUES(2,'GROUP2','GROUP2 DESC') INSERT INTO @USERGROUP VALUES (1,1) INSERT INTO @USERGROUP VALUES (2,2) INSERT INTO @USERGROUP VALUES (3,1) INSERT INTO @GROUPROLES VALUES(1,1) INSERT INTO @GROUPROLES VALUES(2,2) SELECT UserTable.USERID,UserTable.USERFIRSTNAME,UserTable.USERLASTNAME,GroupTable.GROUPNAME FROM @UserTable AS UserTable INNER JOIN @USERGROUP AS UserGroupTable ON UserGroupTable.USERID = UserTable.USERID INNER JOIN @GROUPTABLE AS GroupTable ON GroupTable.GROUPID = UserGroupTable.GROUPID --INNER JOIN @GROUPROLES AS GroupRoles ON GroupTable.GROUPID = GroupRoles.GROUPID WHERE UserTable.USERID = 1
Output is like this...
-- by passing userid as 1 USERID USERFIRSTNAME USERLASTNAME GROUPNAME 1 ABC1 XYZ1 GROUP1
-- by passing userid as 3 USERID USERFIRSTNAME USERLASTNAME GROUPNAME 3 ABC3 XYZ3 GROUP1
-- by passing userid as 2 USERID USERFIRSTNAME USERLASTNAME GROUPNAME 2 ABC2 XYZ2 GROUP2
这篇关于如何从多个表动态选择合适的tableName来创建连接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
