Outputting information from relational databases

[noobie2005]

Hello,

This is the issue at hand, I have a relational database. Two of the tables are
in a relationship, I have a query that allows a person to search by students
and display all there service hours and the where they participated. Now,
here's where I'm having the problem. I want to search backwards, by searching
the service and getting all the students that participated in that event. I get
output, but only 1 record, and there are about 200+. I've attached the code,
maybe I did something wrong? Help is appreciated. Thank you in advance.

<cfquery name="qrecords" datasource="">
SELECT *
FROM HoursLog
WHERE Service = '#form.Service#'
</cfquery>

<cfquery name="qstudents" datasource="">
SELECT *
FROM Students
WHERE StudentID = '#qrecords.StudentID#'
</cfquery>





<tr>
<td colspan="2" height="100%">

<h2>Search Students</h2>


Below is a list of Students from your search of
"<cfoutput><strong>#form.Service#</strong></cfoutput>":
<br><br>

<cfif #qrecords.recordcount# NEQ 0>


<cftable query = "qstudents" startRow = "1" colheaders colSpacing = "6"
HTMLTable border="1" headerlines="2">
<!--- each cfcol tag sets width of a column in table, and specifies header
information and text/CFML with which to fill cell --->
<cfcol header = "<c>Active?</c>"
align = "Left"
width = 2
text = "#Active#">

<cfcol header = "<c>Student ID</c>"
align = "Left"
width = 2
text = "<a
href=displaystudent.cfm?StudentID=#StudentID#>#Stu dentID#</a>">

<cfcol header = "<c>Last Name</c>"
align = "Left"
width = 15
text = "#LastName#">

<cfcol header = "<c>First Name</c>"
align = "Center"
width = 15
text = "#FirstName#">

<cfcol header = "<c>Country</c>"
align = "Center"
width = 15
text = "#Country#">

<cfcol header = "<c>Major</c>"
align = "Center"
width = 15
text = "#Major#">

<cfcol header = "<c>CS Hours</c>"
align = "Center"
width = 15
text = "#CSHours#">
</cftable>


<cfelse>

Sorry, your search for "<cfoutput><strong>#form.Service#</strong></cfoutput>"
did not return any results. Please <a href="index.cfm">search again</a>.
<br><br><br>
</cfif>



</td>
</tr>

[paross1]

You really only need one query.

Phil



<cfquery name="qstudents" datasource="">
SELECT *
FROM Students s
WHERE EXISTS
(SELECT 1
FROM HoursLog h
WHERE h.Service = '#form.Service#'
AND h.StudentID = s.StudentID
</cfquery>

[paross1]

....or, if you need data from both tables, you might try somethin like this.

Phil



<cfquery name="qstudents" datasource="">
SELECT *
FROM Students s
INNER JOIN HoursLog h ON h.StudentID = s.StudentID
WHERE h.Service = '#form.Service#'
</cfquery>

[noobie2005]

Hey Thanks,

It working now, but it repeats. I tried doing a DISTINCT * but that did'nt work. Do I specifiy the DISTINCT attrib on the StudentID?

[noobie2005]

Nevermind. I got it. Thanks!