June 5, 2014 at 11:04 am
In t-sql 2008 r2, 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?
June 5, 2014 at 11:53 am
You need to associate course to the person.
June 5, 2014 at 11:57 am
how would you do that?
June 5, 2014 at 12:08 pm
We would need to know the schema of the tables involved.
June 5, 2014 at 12:19 pm
djj (6/5/2014)
You need to associate course to the person.
How would we know??? We can't see your screen and have no idea what your data structures are like. I would think you probably have a table that handle the many to many relationship between Associates and Courses.
I ran your code through a formatter so the rest of us can read it.
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
I am curious why this is broken apart into two select statements and then joined on a common value from the same table in each query.
As a complete shot in the dark this seems like it is probably the same thing as the query above. Untested of course.
SELECT s.number
,s.homeroomSection
,co.number
,co.NAME
,co.homeroom
,c.calendarID
,p.studentNumber
,e.grade
,c.NAME
,c.endYear
FROM Section s
INNER JOIN Course co ON co.number = s.number
INNER JOIN Calendar c ON co.calendarID = c.calendarID
INNER JOIN Enrollment e ON e.CalendarID = c.CalendarID
INNER JOIN Person p ON p.personID = e.personID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2014 at 12:24 pm
Sean, I got to the your second t-sql and decided something was missing, as you need to associate a person with a course. 🙂
June 5, 2014 at 12:30 pm
djj (6/5/2014)
Sean, I got to the your second t-sql and decided something was missing, as you need to associate a person with a course. 🙂
Agreed. Something is missing...the junction table that holds the values for what course a given associate is related to.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply