How would I write this query? Combining 3 table to as one?

I have 3 tables

1 courses  ((pk)CoursId, Name, Date)  All are single rows of  data
2 courseInstructors  ((pk)courseInstructors(fk)CourseID,IndtructorID) returns multiples instructors for each course
3 instructor  (pk)instructorID, instructorName)


Im trying to get the return values:

1. Course
2. Instructors names
3. Dates of course

How would I write this query

Thanks in advance.
bmanmike39Asked:
Who is Participating?
 
SharathData EngineerCommented:
Are you looking for this?
;with CTE as (
SELECT DISTINCT courses.coursID, courses.name, courses.date, courseInstructors.instructorID, instructor.instructorName,
       row_number() over (partition by courses.coursID, courses.name,courses.date order by courseInstructors.instructorID, instructor.instructorName) rn
FROM            courses INNER JOIN
                         courseInstructors ON courses.coursID = courseInstructors.coursID INNER JOIN
                         instructor ON courseInstructors.instructorID = instructor.instructorID)

select case rn when 1 then coursID else null end as coursID,
       case rn when 1 then name else null end as name,
       case rn when 1 then date else null end as date,
       instructorID,
       instructorName
  from CTE

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
select c.courseid, i.InstructureName , c.date
from Cources c
Left join CourseInstuctors  ci on c.Courseid = ci.courseid
inner join instructor i on i.instructorid  = ci.instructorid
0
 
chapmandewCommented:
select course, instructorname, coursedate
from courses c
join courseinstructors i on c.courseid = i.courseid
join instructor ii on i.instructorid = ii.instructorid
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
chapmandewCommented:
I don't see a reason for the outer join, aneesh....
0
 
fhillyer1Commented:
select a.coursID, a.name, a.date, c.instructorName from courses a join courseinstructors b on b.courseid = a.coursid join instructor c on c.instructorid = b.instructorid

i think
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
i thought he need to list out all the courses ( another guess)
0
 
bmanmike39Author Commented:
SELECT courses.courseID, instructor.InstructureName , courses.date
FROM Courses C
Left join CourseInstructors  courseinstructors on courses.CourseID = courseInstructors.courseID
inner join instructor i on instructor.instructorID  = courseinstructors.instructorID



i get errors

The multi-part identifier "courses.courseID" could not be bound
and
The multi-part identifier "instructor.InstructorsName" could not be bound
0
 
chapmandewCommented:
try mine

select course, instructorname, coursedate
from courses c
join courseinstructors i on c.courseid = i.courseid
join instructor ii on i.instructorid = ii.instructorid
0
 
Chris LuttrellSenior Database ArchitectCommented:
the problem was that you either use the table aliases "C" and "i" everywhere or you use the full table names everywhere, instructor.instructorID , you can't mix the syntax.
chanmandew's should work just fine
0
 
bmanmike39Author Commented:
This works but I need to return the course name and date one time but all of the instructors and their names



SELECT DISTINCT courses.coursID, courses.name, courses.date, courseInstructors.instructorID, instructor.instructorName
FROM            courses INNER JOIN
                         courseInstructors ON courses.coursID = courseInstructors.coursID INNER JOIN
                         instructor ON courseInstructors.instructorID = instructor.instructorID
0
 
Chris LuttrellSenior Database ArchitectCommented:
"all of the instructors and their names" on separate lines each or as one string of output?  And in ont repeating the Course Name and date, is that in the report in RS where it is normally done, or do you have to have the query do it which will  be cumbersome?
0
 
Chris LuttrellSenior Database ArchitectCommented:
In SSRS to hide the duplicates, just set the properties value for the item(s) you want to hide.  It is wanting the name of the dataset or group name as the value.
SSRS2005TextboxProperties.png
0
 
Chris LuttrellSenior Database ArchitectCommented:
yeah Sharath, I agree that would be the way to do it in a query.  But I was trying to get the point across that it is sort  of cumbersome to do in a query for something that can be handled in the Reproting Services interface, which I am thinking the MS SQL Reporting zone inclusion alludes to.  
And then if they need to order/group things by Course or Date in the report, it all backfires on them.  Best to work with the tools as they are meant to be used.
0
 
jaan33Commented:

--list all course regradless if an instructor is assigned
--if an instructor is assigned then get that instructor's name
select c.name as coursename
    ,g.instructorname
    ,c.date
from courses c
left join (select ci.courseid, i.instructorname
           from courseinstructors ci
           join instructor i on i.instructorid = ci.instructorid) g on g.courseid = c.courseid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.