t-sql 2012 inner join - 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

SQL Server 2012 Forums

Transact-SQL (2012)

t-sql 2012 inner join

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-06-05 : 13:05:13

In t-sql 2012, I have 2 select statements that I would like to join by calendarID . I would like to obtain the results in the same query but I keep getting syntax errors.
The first select is the following:
SELECT Section.number, Section.homeroomSection,
Course.number, Course.name, Course.homeroom, Calendar.calendarID
FROM Section
INNER JOIN Course
ON Course.number = Section.number
INNER JOIN Calendar
ON Course.calendarID = Calendar.calendarID

The second select is the following:
SELECT Person.studentNumber, Enrollment.grade, Calendar.calendarID, Calendar.name, Calendar.endYear
FROM Enrollment
INNER JOIN Person
ON Enrollment.personID = Person.personID
INNER Calendar
ON Enrollment.calendarID = Calendar.calendarID

I would like the following columns to display:
Section.number, Section.homeroomSection, Course.number, Course.name, Course.homeroom, Calendar.calendarID
Person.studentNumber, Enrollment.grade, Calendar.name, Calendar.endYear

Thus can you show me how to change the sqls listed above to obtain the results I am looking for?

If possible I would like the sql to look something like the following:

select Section.number, Section.homeroomSection, Course.number, Course.name, Course.homeroom, Calendar.calendarID
Person.studentNumber, Enrollment.grade, Calendar.name, Calendar.endYear
from
(SELECT Section.number, Section.homeroomSection,
Course.number, Course.name, Course.homeroom, Calendar.calendarID
FROM Section
INNER JOIN Course
ON Course.number = Section.number
INNER JOIN Calendar
ON Course.calendarID = Calendar.calendarID) A
inner join
(SELECT Person.studentNumber, Enrollment.grade, Calendar.calendarID, Calendar.name, Calendar.endYear
FROM Enrollment
INNER JOIN Person
ON Enrollment.personID = Person.personID
INNER Calendar
ON Enrollment.calendarID = Calendar.calendarID) b on A.calendarID = B.calendarID

Thus can you show me how to make this t-sql work?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-05 : 14:28:34

Assuming your join on CalendarID is right, something like:

select 
	A.SectionNumber, 
	A.homeroomSection, 
	A.CourseNumber, 
	A.CourseName, 
	A.homeroom, 
	A.calendarID ,
	B.studentNumber, 
	B.grade, 
	B.name, 
	B.endYear 
 from
	(
		 SELECT 
			Section.number AS SectionNumber, 
			Section.homeroomSection, 
			Course.number AS CourseNumber, 
			Course.name AS CourseName, 
			Course.homeroom, 
			Calendar.calendarID 
		 FROM 
			Section 
		 INNER JOIN 
			Course 
			ON Course.number = Section.number 
		 INNER JOIN 
			Calendar 
			ON Course.calendarID = Calendar.calendarID
	) AS A
 inner join 
	(
		SELECT 
			Person.studentNumber, 
			Enrollment.grade, 
			Calendar.calendarID, 
			Calendar.name, 
			Calendar.endYear
		 FROM 
			Enrollment 
		 INNER JOIN 
			Person 
			ON Enrollment.personID = Person.personID
		 INNER 
			Calendar 
			ON Enrollment.calendarID = Calendar.calendarID
	) AS b 
	on A.calendarID = B.calendarID

I suspect that you can code this as one select without derived tables, but I don't know enough about you schema/data to be able to say for sure.

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-06-05 : 17:06:26

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources