Click here to Skip to main content
15,892,161 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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)
Posted
Updated 10-Apr-19 2:04am
v3
Comments
Andy Lanng 10-Apr-19 4:57am    
What is the data? what is your output from the current query? what is the problem?
Member 14156756 10-Apr-19 5:06am    
we have this three table. College, Faculty and Student.
We need to get this result from using three table.
Andy Lanng 10-Apr-19 6:41am    
yup - I missed the data - I see it now
What result do you get from the query in "What I have Tried"?

1 solution

You can't get those results from those tables. Your design is wrong - the only thing connecting the objects is CollegeId so you have no way of knowing which Faculty each student is in.

Instead of CollegeId in the Student table, Faculty would be better. Although bear in mind that would imply that each student could only belong to one faculty

Also don't join tables like that - it's very old-fashioned and prone to errors. See Joining Tables in SQL[^] for the right way.

So you should end up with something LIKE this...
SQL
select c.CollegeId as CollegeId,
	   t.FacultyName   as FacultyName,
       c.CollegeName as CollegeName,
       s.StudentName   as StudentName
from student s
     INNER JOIN Faculty t ON s.FacultyId = t.FacultyId
     INNER JOIN College c ON c.CollegeId = t.CollegeId
order by c.CollegeId
 
Share this answer
 
Comments
Andy Lanng 10-Apr-19 7:01am    
Pretty sure this is the same assignment me see this time every year. I don't think changing the structure is a thing they can do
The joins are wrong but so is the data if that's the expected result [2,B,ddd,null] would not have null.
CHill60 10-Apr-19 8:22am    
You have to wonder at some of these teaching institutions. This isn't the worst example I've seen, but it's definitely up there!
Andy Lanng 10-Apr-19 8:22am    
XD
Member 14156756 10-Apr-19 8:34am    
can you help me, for optimizing?
CHill60 10-Apr-19 8:37am    
What do you mean by optimizing? Stick some indexes on the tables.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900