Sql command to join two tables and self 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

Sql command to join two tables and self join

Author

Topic

sureshsmanian
Starting Member

31 Posts

Posted - 2011-09-14 : 04:38:30

Hi Experts
I have two tables

Table-A
-------
JobInfo (Id, JobId - nvarchar(20)- PK, JobTitle- nvarchar(100))

Table-B
--------
EmployeeInfo( Id, EmpId - nvarchar(20)-PK,
JobId1 - nvarchar(20) - refers JobId-JobInfo,
ReportingToJob1 - nvarchar(20) - refers JobId-JobInfo,
ReportingManager1 - nvarchar(20) - refers some other EmpId - self references EmployeeInfo
JobId2 - nvarchar(20) - refers JobId-JobInfo,
ReportingToJob2 - nvarchar(20) - refers JobId-JobInfo,
ReportingManager2 - nvarchar(20) - refers some other EmpId - self references EmployeeInfo)

*****************************************************************
I want the OUTPUT in this tabular format
==================================================================
Sno|EmpId|Name|JobTitle1|Report_JobTitle1|Report_Mgr1|JobTitle2| Report_JobTitle2|Report_Mgr2
==================================================================
******************************************************************

For your info.
Query for Create and Insert
---------------------------

Table A
------- JobInfo

CREATE TABLE [dbo].[JobInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[JobId] [nvarchar](20) NOT NULL,
[JobTitle] [nvarchar](150) NULL,
PRIMARY KEY CLUSTERED
(
[JobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]
) ON [PRIMARY]

------

Insert into JobInfo(JobId,JobTitle)
Select 'J001', 'Director'
Union all
Select 'J002', 'Driver'
Union all
Select 'J003', 'Messenger'
Union all
Select 'J004', 'Tech Manager'
Union all
Select 'J005', 'SW Manager',
Union all
Select 'J006', 'Sr Architect'
Union all
Select 'J00100', 'CEO'

Table B : EmployeeInfo
------
CREATE TABLE [dbo].[EmployeeInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmpId] [nvarchar](20) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[JobId1] [nvarchar](20) NULL,
[ReportingJobId1] [nvarchar](20) NULL,
[ReportingManager1] [nvarchar](20) NULL,
[JobId2] [nvarchar](20) NULL,
[ReportingJobId2] [nvarchar](20) NULL,
[ReportingManager2] [nvarchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[EmpId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[EmployeeInfo] WITH CHECK ADD CONSTRAINT [FK_EmployeeInfo_JobInfo1] FOREIGN KEY([JobId1])
REFERENCES [dbo].[JobInfo] ([JobId])
GO

ALTER TABLE [dbo].[EmployeeInfo] CHECK CONSTRAINT [FK_EmployeeInfo_JobInfo1]
GO

ALTER TABLE [dbo].[EmployeeInfo] WITH CHECK ADD CONSTRAINT [FK_EmployeeInfo_JobInfo2] FOREIGN KEY([JobId2])
REFERENCES [dbo].[JobInfo] ([JobId])
GO

ALTER TABLE [dbo].[EmployeeInfo] CHECK CONSTRAINT [FK_EmployeeInfo_JobInfo2]
GO

ALTER TABLE [dbo].[EmployeeInfo] WITH CHECK ADD CONSTRAINT [FK_EmployeeInfo_ReportingJobInfo1] FOREIGN

KEY([ReportingJobId1])
REFERENCES [dbo].[JobInfo] ([JobId])
GO

ALTER TABLE [dbo].[EmployeeInfo] CHECK CONSTRAINT [FK_EmployeeInfo_ReportingJobInfo1]
GO

ALTER TABLE [dbo].[EmployeeInfo] WITH CHECK ADD CONSTRAINT [FK_EmployeeInfo_ReportingJobInfo2] FOREIGN

KEY([ReportingJobId2])
REFERENCES [dbo].[JobInfo] ([JobId])
GO

ALTER TABLE [dbo].[EmployeeInfo] CHECK CONSTRAINT [FK_EmployeeInfo_ReportingJobInfo2]
GO

ALTER TABLE [dbo].[EmployeeInfo] WITH CHECK ADD CONSTRAINT [FK_EmployeeInfo_ReportingManager1] FOREIGN

KEY([ReportingManager1])
REFERENCES [dbo].[EmployeeInfo] ([EmpId])
GO

ALTER TABLE [dbo].[EmployeeInfo] CHECK CONSTRAINT [FK_EmployeeInfo_ReportingManager1]
GO

ALTER TABLE [dbo].[EmployeeInfo] WITH CHECK ADD CONSTRAINT [FK_EmployeeInfo_ReportingManager2] FOREIGN

KEY([ReportingManager2])
REFERENCES [dbo].[EmployeeInfo] ([EmpId])
GO

ALTER TABLE [dbo].[EmployeeInfo] CHECK CONSTRAINT [FK_EmployeeInfo_ReportingManager2]
GO

=================================================

insert into employeeInfo(EmpId,Name,JobId1,ReportingJobId1,ReportingManager1,
JobId2,ReportingJobId2,ReportingManager2)
Select 'E001','Ameen','J00100','J00100',NULL,NULL,NULL,NULL
Union All
select 'E002','Bernice', 'J004','J001','E006','J005','J006','E007'
Union All
Select 'E003','Samantha','J005','J001','E006',NULL,NULL,NULL
Union All
Select 'E005', 'Alex', 'J002','J001','E006', 'J003','J004','E002'
Union All
Select 'E006', 'Ahmed', 'J001','J00100','E001',NULL,NULL,NULL
Union All
Select 'E007', 'Ankur', 'J006','J00100','E001',NULL,NULL,NULL

-----------------

Thanks for your help.

Regards
SSM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 08:46:57

sounds like set of joins


SELECT e1.EmpId,
jf1.JobTitle AS JobTitle1,
jf2.JobTitle AS Report_JobTitle1,
e2.EmpId AS ReportingManager1,
jf3.JobTitle AS JobTitle2,
jf4.JobTitle AS Report_JobTitle2,
e3.EmpId  AS Report_Mgr2
FROM EmployeeInfo e1
INNER JOIN JobInfo jf1
ON jf1.Job_ID = e.Job_ID
INNER JOIN JobInfo jf2
ON jf2.Job_ID = e.ReportingToJob1 
INNER JOIN EmployeeInfo e2
ON e2.EmpId = e1.ReportingManager1 
INNER JOIN JobInfo jf3
ON jf3.Job_ID = e2.Job_ID
INNER JOIN JobInfo jf4
ON jf4.Job_ID = e2.ReportingToJob1
INNER JOIN EmployeeInfo e3
ON e3.EmpId = e1.ReportingManager2

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

sureshsmanian
Starting Member

31 Posts

Posted - 2011-09-15 : 07:03:49

Thanks @Vishak16,
I have written with set of joins then it is working(similar to your query).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 07:23:28

ok fine...you're welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources