We have three table. We need to get result as per i declare after the question.
CREATE TABLE College
(
CollegeId INT IDENTITY(1, 1)
,CollegeName VARCHAR(20)
,CONSTRAINT PK_College_CollegeId
PRIMARY KEY (CollegeId)
)
CREATE TABLE Faculty
(
CollegeId INT
,FacultyName VARCHAR(20)
,CONSTRAINT FK_College_Faculty_CollegeId
FOREIGN KEY (CollegeId)
REFERENCES College (CollegeId)
)
CREATE TABLE Student
(
CollegeId INT
,StudentName VARCHAR(20)
,CONSTRAINT FK_College_Student_CollegeId
FOREIGN KEY (CollegeId)
REFERENCES College (CollegeId)
)
INSERT INTO College
VALUES
('A')
,('B')
,('C')
INSERT INTO Faculty
VALUES
(1, 'aaa')
,(1, 'bbb')
,(2, 'ccc')
,(2, 'ddd')
INSERT INTO Student
VALUES
(1, 'xxx')
,(1, 'yyy')
,(1, 'zzz')
,(2, 'www')
SELECT * FROM College
SELECT * FROM Faculty
SELECT * FROM Student
OUT PUT will be :-
<pre>CollegeId
CollegeName FacultyName StudentName
1 A aaa xxx
1 A bbb yyy
1 A NULL zzz
2 B ccc www
2 B ddd NULL
3 C NULL NULL
What I have tried:
SELECT COALESCE(cf.CollegeId,cs.CollegeId) AS CollegeId,
COALESCE(cf.CollegeName,cs.CollegeName) AS CollegeName,
cf.FacultyName,
cs.StudentName
FROM
(SELECT c.CollegeId,
c.CollegeName,
f.FacultyName,
ROW_NUMBER () OVER (PARTITION BY f.CollegeId ORDER BY f.CollegeId) AS row_num
FROM College c FULL JOIN
Faculty f ON c.CollegeId = f.CollegeId)cf FULL JOIN
(SELECT c.CollegeId,
c.CollegeName,
s.StudentName,
ROW_NUMBER () OVER (PARTITION BY s.CollegeId ORDER BY s.CollegeId) AS row_num
FROM College c FULL JOIN
Student s ON c.CollegeId = s.CollegeId)cs ON cf.row_num = cs.row_num AND
cf.CollegeId = cs.CollegeId
ORDER BY COALESCE(cf.CollegeId,cs.CollegeId)