I have two sql queries: 1:
select student.name , subject.name as s1 , teacher.name
from student
inner join teacher
on student.[subj2-t-id] = teacher.[t-id]
inner join subject
on teacher.[s-id] = subject.[s-id]
2:
select student.[std-id] , subject.name as s2 , teacher.[t-id]
from student
inner join teacher
on student.[subj1-t-id] = teacher.[t-id]
inner join subject
on teacher.[s-id] = subject.[s-id]
Now I want to combine these two results but the columns should be separated in resultant table, like one table with six columns, 3 of first query result and 3 of second query result. Please help me, how can I do this ?
Here is some demo data which represents what I am working with on this problem: Department Table:
d-id name
111 English
112 IT
Subject Table :
s-id name d-id
234 Eng-com 111
235 Data Str 112
236 OS 112
237 Grammer 111
Teacher Table :
t-id name d-id s-id
60 Ahmed 112 236
61 Ali 111 234
62 Nawaz 111 237
63 Imran 112 235
Student Table:
std-id name d-id subj1-t-id subj2-t-id
20 Hassan 112 63 61
21 Momin 112 60 62
22 Hussain 111 61 63
23 Najeeb 112 63 62
24 Qasim 111 62 60
Here in student table subject1 and subject2 attributes are teacherIDs from teacher table. Both are FK. Similarly D-ID and S-ID stands for department id and subject id are FK in teacher table. and D-ID is FK in Subject table.
CodePudding user response：
Making some considerable assumptions which may alter the required output. First, using some demo data:
DECLARE @Student TABLE ([std-id] INT IDENTITY, Name NVARCHAR(50), [Subj1-t-id] INT, [Subj2-t-id] INT)
DECLARE @Teacher TABLE ([t-id] INT IDENTITY, Name NVARCHAR(50), [s-id] INT)
DECLARE @Subject TABLE ([s-id] INT IDENTITY, Name NVARCHAR(50))
INSERT INTO @Subject (name) VALUES
('Math'),('Science'),('English')
INSERT INTO @Teacher (name, [s-id]) VALUES
('Mr Maths',1),('Mrs Science',1),('Mrs Science',2),('Dr English',3)
INSERT INTO @Student (Name, [Subj1-t-id], [Subj2-t-id]) VALUES
('Pike',3,1),('Kirk',1,NULL),('Picard',NULL,3),('Riker',2,1)
We can query these tables to produce the requested out put in a single query by adding secondary joins to the teacher and subject tables:
select student.name , subject.name as s1 , teacher.name, student.[std-id] , subject2.name as s2 , teacher2.[t-id]
FROM @student AS student
LEFT join @teacher AS teacher
ON student.[subj2-t-id] = teacher.[t-id]
LEFT join @subject AS subject
ON teacher.[s-id] = subject.[s-id]
LEFT OUTER JOIN @teacher AS teacher2
ON student.[subj1-t-id] = teacher2.[t-id]
LEFT OUTER join @subject AS subject2
ON teacher2.[s-id] = subject2.[s-id]
You'll notice all the joins are LEFT OUTER. This is because (depending on your schema definition) students could have 1 or 2 subjects, with either of the subject columns being NULL. We probably still want these students to appear in the result set. Each of the tables has had an alias added so we can refer to it uniquely in the query. First we join student to teacher on the first subject column, and then that to subject. Then we join back to the teacher table again, on the other subject column and on to subject using that. We now have all six required columns available.
name s1 name std-id s2 t-id
------------------------------------------------
Pike Math Mr Maths 1 Science 3
Kirk NULL NULL 2 Math 1
Picard Science Mrs Science 3 NULL NULL
Riker Math Mr Maths 4 Math 2
Things that should be fixed:
- Table names should often be pluralized. In this case the student table contains 1 to many students, and it's name should reflect that.
- A table should describe an entity (and only that entity) as completely as possible.
- Illegal characters and reserved words should be avoided in object names, unless it's unavoidable. Data should be normalized.
- Having columns such as "Teacher1" and "Teacher2" build in limitations to your design which WILL cause problems later. Instead lookup objects can be used.
- Alias' should ALWAYS be used for tables.
Consider the following:
DECLARE @StudentClasses TABLE (StudentID INT, ClassID INT)
DECLARE @Teachers TABLE (TeacherID INT IDENTITY, Name NVARCHAR(50), ClassID INT)
DECLARE @Subjects TABLE (SubjectID INT IDENTITY, Name NVARCHAR(50))
DECLARE @Classes TABLE (ClassID INT IDENTITY, Name NVARCHAR(50), TeacherID INT, SubjectID INT)
INSERT INTO @Subjects (Name) VALUES
('Math'),('Science'),('English')
INSERT INTO @Teachers (Name, ClassID) VALUES
('Mr Maths',1),('Mrs Science',1),('Mrs Science',2),('Dr English',3)
INSERT INTO @Classes (Name, TeacherID, SubjectID) VALUES
('Math 101', 2, 1),('Math 102', 1, 1),
('Science 101', 2, 2),('Science 101', 2, 2),
('English 101', 1, 3),('English Lit', 1, 3),
('English 900', 3, 3)
INSERT INTO @Students (Name) VALUES
('Pike'),('Kirk'),('Picard'),('Riker')
INSERT INTO @StudentClasses (StudentID, ClassID) VALUES
(1,1),(1,2),(1,3),(2,2),(2,3),(2,4),
(3,2),(4,4),(4,5)
SELECT s.StudentID, s.Name AS StudentName, c.Name AS ClassName, t.Name AS TeacherName, su.name AS SubjectName
FROM @Students s
INNER JOIN @StudentClasses sc
ON s.StudentID = sc.StudentID
INNER JOIN @Classes c
ON sc.ClassID = c.ClassID
INNER JOIN @Teachers t
ON c.TeacherID = t.TeacherID
INNER JOIN @Subjects su
ON c.SubjectID = su.SubjectID
ORDER BY StudentID
StudentID StudentName ClassName TeacherName SubjectName
-----------------------------------------------------------
1 Pike Math 101 Mrs Science Math
1 Pike Math 102 Mr Maths Math
1 Pike Science 101 Mrs Science Science
2 Kirk Math 102 Mr Maths Math
2 Kirk Science 101 Mrs Science Science
2 Kirk Science 101 Mrs Science Science
3 Picard Math 102 Mr Maths Math
4 Riker Science 101 Mrs Science Science
4 Riker English 101 Mr Maths English