How to add two SQL queries results in one resultant table with different columns for each query result?

Question

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:

DECLARE @Department TABLE ([d-id] INT, name NVARCHAR(50))
DECLARE @Subject TABLE ([s-id] INT, name NVARCHAR(50), [d-id] INT)
DECLARE @Teacher TABLE ([t-id] INT, name NVARCHAR(50), [d-id] INT, [s-id] INT)
DECLARE @Student TABLE ([std-id] INT, name NVARCHAR(50), [d-id] INT, [subj1-t-id] INT, [subj2-t-id] INT)

INSERT INTO @Department ([d-id], name) VALUES 
(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.

Judging by the limited information you've provided you have some significant schema issues including badly named columns. When asking a question like this you should be providing demo data which clearly shows example data and your object structures (DDL and DML) and be easily reproducible. — Patrick Hurst
– Patrick Hurst, Commented Dec 26, 2022 at 14:36
Please do not use images to describe your data and objects, these are not very useful and do not provide reproducible examples. — Patrick Hurst
– Patrick Hurst, Commented Dec 26, 2022 at 15:13
Sure Sir , I have added some more information. Please take a look — Hassan Ali
– Hassan Ali, Commented Dec 26, 2022 at 15:34
Did you try the answer already provided? It already seems to solve your issue. When providing demo data please do so in a fashion which is easily reproducible, as was in the previous edit. — Patrick Hurst
– Patrick Hurst, Commented Dec 26, 2022 at 15:50

Patrick Hurst · Accepted Answer · 2022-12-26 15:24:14Z

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

Collectives™ on Stack Overflow

How to add two SQL queries results in one resultant table with different columns for each query result?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related