Sql Query to join two tables - SQL Server Forums

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 Query to join two tables

Author

Topic

sureshmanian
Starting Member

26 Posts

Posted - 2012-04-25 : 08:06:24

Dear all
I have two tables: Supervisor and student.
I wanted to display the supervisor names against students name.

Relation : Supervisor
ID - bigint - not null - auto increment
SupervisorId -- nvarchar(20) - NOT NULL, -- Primary key
Name -- nvarchar(50) - not null

Relation : Student
ID -- bignint - not null - auton increment
StudentId - nvarchar (20) - NOT NULL
StudentName nvarchar (50) - NULL
Supervisor1 nvarchar (20) - NULL - refer supervisorid of Supervisor table
Supervisor2 nvarchar (20) - NULL - refer supervisorid of Supervisor table
Supervisor3 nvarchar (20) - NULL - refer supervisorid of Supervisor table

SQl commands:
----------

CREATE TABLE [dbo].[Supervisor](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[SupervisorId] [nvarchar](20) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Manager] PRIMARY KEY CLUSTERED
(
[SupervisorId] 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

-----------

CREATE TABLE [dbo].[Student](
[StudentId] [nvarchar](20) NOT NULL,
[StudentName] [nvarchar](50) NULL,
[Supervisor1] [nvarchar](20) NULL,
[Supervisor2] [nvarchar](20) NULL,
[Supervisor3] [nvarchar](20) NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[StudentId] 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].[Student] WITH CHECK ADD CONSTRAINT [FK_Student_Supervisor] FOREIGN KEY([Supervisor1])
REFERENCES [dbo].[Supervisor] ([SupervisorId])
GO

ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Supervisor]
GO

ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [FK_Student_Supervisor1] FOREIGN KEY([Supervisor2])
REFERENCES [dbo].[Supervisor] ([SupervisorId])
GO

ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Supervisor1]
GO

ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [FK_Student_Supervisor2] FOREIGN KEY([Supervisor3])
REFERENCES [dbo].[Supervisor] ([SupervisorId])
GO

ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Supervisor2]
GO
--------------------------

Insert into Supervisor
Select 'S001', 'Karan'
Union
Select 'S002', 'Naseema'
Union
Select 'S003', 'Jose'

--------

Insert into Student
Select 'T101', 'Kajol', 'S001', 'S002','S003'
union
Select 'T102', 'Maya', 'S003', 'S002', "S001';

-======================

Required output:

Student | Supervisor1 | Supervisor2 | Supervisor3
---------------------------------------------------
Kajol | Karan | Nasseema | Jose
Maya | Jose | Nassema | Karan

Thanks & Regards
SSM

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-25 : 08:53:44

select
st.StudentName,
sv1.Name as Supervisor1,
sv2.Name as Supervisor2,
sv3.Name as Supervisor3
from Student as st
left join Supervisor as sv1 on sv1.SupervisorId = st.Supervisor1
left join Supervisor as sv2 on sv2.SupervisorId = st.Supervisor2
left join Supervisor as sv3 on sv3.SupervisorId = st.Supervisor3

No, you're never too old to Yak'n'Roll if you're too young to die.

sureshmanian
Starting Member

26 Posts

Posted - 2012-04-25 : 10:03:40

Thank you webfred..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources