Simple JOIN help needed

[benphp]

Hi, this will be easy for anyone who knows SQL better than I.

 

I have 4 tables.

 

tblCourses

tblClasses

tblClass_Students

tblEmployee

 

There's a ClassID, CourseID, EID that links these together, and I can get a list of classes and dates using this SQL:

 

Select DISTINCT
tblClasses.ClassDate,  
tblClasses.ClassID,
tblClass_Students.EID
FROM tblClasses
LEFT OUTER JOIN tblCourses ON (tblClasses.CourseID=tblCourses.CourseID)
LEFT OUTER JOIN tblClass_Students ON (tblClasses.ClassID=tblClass_Students.ClassID)
Where  
tblCourses.SUID = $SUID
AND
tblCourses.CourseID = $temp
AND
tblCourses.Type_10 = '$active'
ORDER BY tblClasses.ClassDate DESC

 

How can I get the employee names - tblEmployee.Last, tblEmployee.First?

 

Or do I have to write a query within the query results?

 

Thanks!

 

B

 

 

[fenway]

What's wrong with another join?

[benphp]

I tried this:

 

Select DISTINCT
   tblClasses.ClassDate, 
   tblClasses.ClassID,
   tblClass_Students.EID,
   tblEmployee.Last
FROM tblClasses
LEFT OUTER JOIN tblCourses ON (tblClasses.CourseID=tblCourses.CourseID)
LEFT OUTER JOIN tblClass_Students ON (tblClasses.ClassID=tblClass_Students.ClassID)
LEFT OUTER JOIN tblEmployee ON (tblCourses.EID=tblEmployee.EID)
Where 
   tblCourses.SUID = $SUID
AND
   tblCourses.CourseID = $temp
AND
   tblCourses.Type_10 = '$active'
ORDER BY tblClasses.ClassDate DESC

 

But it doesn't work - the syntax is wrong - probably because I'm not joining the select table -  and I don't know SQL well enough to know how to stitch it together. How would you write that join?

 

[kickstart]

Hi

 

Nothing jumps out. What is the error you are getting?

 

By the way, as you are checking values from tblCourses in the WHERE clause there is no point to using a LEFT OUTER JOIN. That would get rows where there is no matching row on tblCourses which the WHERE clause would then throw away.

 

All the best

 

Keith

[benphp]

Keith - thanks! It was a table naming problem 

 

Your confirmation that it looked okay made me look at other issues.  This one worked:

 

Select DISTINCT
tblClasses.ClassDate,  
tblClasses.ClassID,
tblClass_Students.EID,
tblEmployee.Last,
tblEmployee.First,
tblClass_Students.Grade
FROM tblClasses
LEFT OUTER JOIN tblCourses ON (tblClasses.CourseID=tblCourses.CourseID)
LEFT OUTER JOIN tblClass_Students ON (tblClasses.ClassID=tblClass_Students.ClassID)
LEFT OUTER JOIN tblEmployee ON (tblClass_Students.EID=tblEmployee.EID)
Where  
	tblClasses.CourseID = $CourseID
AND
	tblClass_Students.Grade LIKE 'P%'
$ORDERBY