how to choose proper tableName dynamically from multiple tables for creating join query

Question

0.00/5 (No votes)

See more:

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.

Posted 10-Mar-14 1:48am

Alia Sharma

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tejas Vaishnav · Answer 1 · 2014-03-10T03:08:00

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

SQL

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