Problem while applying inner join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem while applying inner join

Author

Topic

snagar
Starting Member

14 Posts

Posted - 2011-11-13 : 10:35:52

I have to apply inner join on two table Table 1 and Table 2
Table 1 has field EMPId and EMPNAME and EMPMANAGERID
EMPID field has unique values.

Table 2 has field EMPMANAGER ID which has matching values with field EMPID of table 2 .
NOW i want to get emp name when i have emp manager id with me so i have use below query-
SELECT T1. EMPNAME from TABLE 1 T1 INNER JOIN TABLE 2 T2 ON
T1.EMPID = T2.EMPMANAGERID

But i am not getting correct results as EMPMANAGERID IS NOT A PRIMARY KEY . I am getting EMPNAME of EMPID and not of EMPMANAGER.

Please help me.
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-13 : 11:50:54

Table 2 has field EMPMANAGER ID which has matching values with field EMPID of table 2
does this mean table has a self relation?

then whats the relevance of EMPMANAGERID in table1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snagar
Starting Member

14 Posts

Posted - 2011-11-13 : 13:34:36

quote:
Originally posted by visakh16

Table 2 has field EMPMANAGER ID which has matching values with field EMPID of table 2
does this mean table has a self relation?

then whats the relevance of EMPMANAGERID in table1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry field EMPMANAGERID was not there in Table 1.
Instead field EMPID and EMPMANAGERID is there in Table 2. Now i want to retrive EMPNAME from table 1 based on EMPMANAGERId from Table 2.
Now since EMPMANAGER ID will have matching values with EMPId from table 1 so i can get EMP NAME of those managers. But there are multiple matchings of empid in table 1 and manager id in table 2 as there are more than 1 employee with same manager so i am facing problems in INNER join.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 00:25:52

[code]
SELECT T1.EMPNAME ,T11.EMPNAME AS MGRNAME from TABLE 1 T1
INNER JOIN TABLE 2 T2 ON
T1.EMPID = T2.EMPID
INNER JOIN TABLE 1 T11
ON T11.EMPID =T2.EMPMANAGERID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snagar
Starting Member

14 Posts

Posted - 2011-11-14 : 02:39:22

quote:
Originally posted by visakh16

SELECT T1.EMPNAME ,T11.EMPNAME AS MGRNAME from TABLE 1 T1
INNER JOIN TABLE 2 T2 ON
T1.EMPID = T2.EMPID
INNER JOIN TABLE 1 T11
ON T11.EMPID =T2.EMPMANAGERID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks a Lot for your help. It works.
I have one more query. Now i have below mentioned scenario-

Table 1 Table 2
---------- ---------------
EMPID EMPNAME SUPV_ID EMPID MANAGERID

Now i want to retrieve following details-
MANGERID, MANAGERNAME, SUPV_ID, SUPV_NAME

I am succedded in getting these details using a third table with fields EMPID and SUPV_ID using below query-

SELECT T2.MANGERID, T11.EMPNAME AS MANAGERNAME, T12.SUPV_ID, T13.EMPNAME AS SUPN_NAME
FROM
TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.EMPID = T2.EMPID
INNER JOIN TABLE1 T11 ON T11.EMPID = T2.MANAGERID
INNER JOIN TABLE3 T12 ON T2.EMPID = T12.EMPID
INNER JOIN TABLE1 T13 ON T13.EMPID = T12.SUPV_ID

So is there any way to use only TABLE1 and TABLE 2 for desired result without using TABLE3

snagar
Starting Member

14 Posts

Posted - 2011-11-14 : 02:43:58

quote:
Originally posted by snagar

quote:
Originally posted by visakh16

SELECT T1.EMPNAME ,T11.EMPNAME AS MGRNAME from TABLE 1 T1
INNER JOIN TABLE 2 T2 ON
T1.EMPID = T2.EMPID
INNER JOIN TABLE 1 T11
ON T11.EMPID =T2.EMPMANAGERID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks a Lot for your help. It works.
I have one more query. Now i have below mentioned scenario-

Table 1 Table 2
---------- ---------------
EMPID EMPNAME SUPV_ID EMPID MANAGERID

Now i want to retrieve following details-
MANGERID, MANAGERNAME, SUPV_ID, SUPV_NAME

I am succedded in getting these details using a third table with fields EMPID and SUPV_ID using below query-

SELECT T2.MANGERID, T11.EMPNAME AS MANAGERNAME, T12.SUPV_ID, T13.EMPNAME AS SUPN_NAME
FROM
TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.EMPID = T2.EMPID
INNER JOIN TABLE1 T11 ON T11.EMPID = T2.MANAGERID
INNER JOIN TABLE3 T12 ON T2.EMPID = T12.EMPID
INNER JOIN TABLE1 T13 ON T13.EMPID = T12.SUPV_ID

So is there any way to use only TABLE1 and TABLE 2 for desired result without using TABLE3

Table fields in previous post are not clear so below are clear details of fields in each table-
Table1- EMPID, EMPNAME, SUPV_ID
Table2- EMPID, MANAGERID
Table3- EMPID, SUPV_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 03:19:41

is SUPV_ID having fk to EMPID? also why should you need a separate table if you already have SUPV_ID
in table1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snagar
Starting Member

14 Posts

Posted - 2011-11-14 : 04:16:38

quote:
Originally posted by visakh16

is SUPV_ID having fk to EMPID? also why should you need a separate table if you already have SUPV_ID
in table1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

The third table is already there in database and SUPV_ID is not having fk to EMPID.
All i need is to get Supervisor Name using same concept as i am getting MANAGER Name.
So can i get supervisor name using supv_id field of Table1 whose supv_id matches emp_id of Table 2. I need result along with manager name and manager id which i am getting through query suggested by you.
Else i am getting it using Third table as i mentioned in my query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 06:35:46

[code]SELECT T1.EMPNAME ,T11.EMPNAME AS MGRNAME,T12.EMPNAME AS SUPVNAME from TABLE 1 T1
INNER JOIN TABLE 2 T2 ON
T1.EMPID = T2.EMPID
INNER JOIN TABLE 1 T11
ON T11.EMPID =T2.EMPMANAGERID
INNER JOIN TABLE 1 T12
On T12.EMPID = T1.SUPV_ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snagar
Starting Member

14 Posts

Posted - 2011-11-14 : 07:12:37

quote:
Originally posted by visakh16

SELECT T1.EMPNAME ,T11.EMPNAME AS MGRNAME,T12.EMPNAME AS SUPVNAME from TABLE 1 T1
INNER JOIN TABLE 2 T2 ON
T1.EMPID = T2.EMPID
INNER JOIN TABLE 1 T11
ON T11.EMPID =T2.EMPMANAGERID
INNER JOIN TABLE 1 T12
On T12.EMPID = T1.SUPV_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks a lot, it works and performance improved as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 07:16:30

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources