[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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.
0
bmanmike39
Asked:
bmanmike39
  • 4
  • 3
  • 2
  • +4
1 Solution
 
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
 
chapmandewCommented:
I don't see a reason for the outer join, aneesh....
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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
 
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
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now