Multiple Joins in one Query for CFGRID

[jbreslow]

Hello,

I am tring to combine all 4 queries into 1 so that it will work in a CFGRID
and it's a little beyond my SQL experience. I have all this working (see code
below) and decided that using the CFGRID would allow and Excel user to be able
to edit the records easier.

I have a table of User's IDs.

student_id, senior_advisor_id, faculty_advisor_id, osa_advisor_id, etc..

Based on those IDs, I need to be able to grab each user's information (ie, get
the student name from one table and their email address from another table. The
same goes for the senior advisor, faculty advisor and osa advisor.) How do I do
this?

<cfquery datasource="#session.dsn#" name="qSelectAdvisorMentor">
select u.user_type_id, ui.edcom_id, ui.firstname, ui.lastname, ui.email, am.*
from users u, user_info ui, advisor_mentor am
where u.edcom_id = ui.edcom_id
and ui.edcom_id = am.student_edcom
and u.user_type_id IN (1101, 1111)
order by u.user_type_id ASC, ui.lastname asc, ui.firstname asc
</cfquery>

<table border="1" cellpadding="4" cellspacing="0" width="100%">
<tr>
<th>Report</th>
<th>Student</th>
<th>Senior Mentor</th>
<th>Faculty Mentor</th>
<th>OSA Advisor</th>
<th>Big Sib</th>
<th>Senior Faculty Advisor</th>
</tr>
<cfoutput query="qSelectAdvisorMentor">
<cfquery datasource="#session.dsn#" name="qSelectSeniorMentorInfo">
select ui.edcom_id, ui.firstname, ui.lastname, ui.email
from users u, user_info ui
where u.edcom_id = ui.edcom_id
and u.edcom_id = <cfqueryparam
value="#qSelectAdvisorMentor.senior_mentor_edcom#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>
<cfquery datasource="#session.dsn#" name="qSelectFacultyMentorInfo">
select ui.edcom_id, ui.firstname, ui.lastname, ui.email
from users u, user_info ui
where u.edcom_id = ui.edcom_id
and u.edcom_id = <cfqueryparam
value="#qSelectAdvisorMentor.faculty_mentor_edcom# " cfsqltype="CF_SQL_VARCHAR">
</cfquery>
<cfquery datasource="#session.dsn#" name="qSelectOSAAdvisorInfo">
select ui.edcom_id, ui.firstname, ui.lastname, ui.email
from users u, user_info ui
where u.edcom_id = ui.edcom_id
and u.edcom_id = <cfqueryparam
value="#qSelectAdvisorMentor.osa_advisor_edcom#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>
<tr bgcolor=###iif(qSelectAdvisorMentor.currentrow mod 2, de ('eeeeee'), de
('d4d4d4'))#>
<td align="center"><a href="report.cfm?id=#qSelectAdvisorMentor.edcom_id #"
target="_blank">Report View</a></td>
<td>&nbsp;<a
href="mailto:#qSelectAdvisorMentor.email#">#qSelec tAdvisorMentor.lastname#,
#qSelectAdvisorMentor.firstname#</a></td>
<td><cfif qSelectSeniorMentorInfo.edcom_id NEQ ""><!---
#qSelectSeniorMentorInfo.edcom_id# ---><a
href="mailto:#qSelectSeniorMentorInfo.email#">#qSe lectSeniorMentorInfo.lastname#
, #qSelectSeniorMentorInfo.firstname#</a><cfelse>&nbsp;</cfif></td>
<td><cfif qSelectFacultyMentorInfo.edcom_id NEQ ""><!---
#qSelectFacultyMentorInfo.edcom_id# ---><a
href="mailto:#qSelectFacultyMentorInfo.email#">#qS electFacultyMentorInfo.lastnam
e#, #qSelectFacultyMentorInfo.firstname#</a><cfelse>&nbsp;</cfif></td>
<td><cfif qSelectOSAAdvisorInfo.edcom_id NEQ ""><!---
#qSelectOSAAdvisorInfo.edcom_id# ---><a
href="mailto:#qSelectOSAAdvisorInfo.email#">#qSele ctOSAAdvisorInfo.lastname#,
#qSelectOSAAdvisorInfo.firstname#</a><cfelse>&nbsp;</cfif></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</cfoutput>
</table>


I'd really appreciate the help.

Thanks

Josh

[mxstu]

Can you briefly state the names of the tables involved and the relationships between them (ie. Primary Keys/Foreign Keys)?