how to modify sql statement - not displaying properly

[frogggg]

I have 2 pages - one accepts all sorts of criteria from the user to search by, and the second has a sql statement which retrieves any records which match all criteria.
Here's the statement:
SELECT JobCategory.JobCatName, CareerLevel.CareerName, CandidateContactInfo.CandidateID, Locations.LocationName, SkillCategory.SkillName " _
& "FROM CandidateContactInfo, CandidateJobCategory, JobCategory, CareerLevel, Locations, CandidateLocations, SkillCategory, CandidateSkills " _
& "WHERE CandidateJobCategory.JobCatID = " & session("SearchJobCategory&quot _
& "AND CandidateJobCategory.JobCatID = JobCategory.JobCatID " _
& "AND CandidateContactInfo.CareerLevel = CareerLevel.CareerID " _
& "AND CandidateContactInfo.CareerLevel = " & session("SearchCareer&quot _
& "AND CandidateLocations.LocationID = " & session("SearchLocation&quot _
& "AND CandidateLocations.LocationID = Locations.LocationID " _
& "AND CandidateSkills.SkillID = SkillCategory.SkillID " _
& "AND CandidateSkills.SkillID IN (" & skills & &quot" _
& "AND CandidateContactInfo.CandidateID = CandidateJobCategory.CandidateID

This works fine. Then I display the results of the query in a grid. The problem is that I would like to allow multiple select in each of the categories. I already did it for skills, as you can (sort of) see. But when displayed in the grid, I get 2 entries, one for each selected skill.

What do I need to change in my statement, or in my grid display, to get it to show one line for each candidate with all the information about that candidate:
job categories, careers, locations, skills, candidateID
job categories, etc. next candidateID
instead of
job category, etc. candidateID
job category, etc. same candidateID

I think this sounds pretty complicated, but I hope someone can figure out what I want and point me in the right direction! Thanks!

 

[Turkbear]

I use the folowing code to display all the employees in Job Classes selected on a previous page by the user ( they can select an unlimited number of Titles) The code lays out a table that lists , beneath each Title, the employees in that job class, with their Office and work phone number - It also counts them...

The snippet that follows uses an array containing the jobclass and employeename:

<%
iRecFirst   = LBound(arrDBData, 2)
iRecLast    = UBound(arrDBData, 2)
iFieldFirst = LBound(arrDBData, 1)
iFieldLast  = UBound(arrDBData, 1)

' Display a table of the data in the array.
' We loop through the array displaying the values.
%>
<table border=&quot;1&quot;>
<%
' Loop through the records (second dimension of the array)
CurOfc = &quot;&quot;
cnt = 0
For I = iRecFirst To iRecLast
	' A table row for each record
	
	Response.Write &quot;<tr>&quot; & vbCrLf
	
	' Loop through the fields (first dimension of the array)
	If CurJob <> arrDBData(0,I) then 
	if cnt > 0 then
	Response.Write &quot;<tr BGCOLOR='66FF99'>&quot; & vbCrLf
	Response.Write &quot;<td><br>&quot; & &quot;Count in This Job Class :&quot; & &quot;</b></td>&quot; & vbTab & &quot;<td><b>&quot; &  cnt & &quot;</b></td>&quot;
	Response.Write &quot;</tr>&quot;
	cnt = 0
	end if
	 CurJob = arrDBData(0,I)
	' A table cell for each field
	Response.Write &quot;<tr BGCOLOR='#00FFFF'>&quot;
	 Response.Write vbTab & &quot;<td><H3>&quot; &  arrDBData(0, I) &  &quot;</td></b>&quot; & vbTab & &quot;<td><b>Name</td></H3>&quot; & vbTab & &quot;<td>&quot; & &quot;</td>&quot; & &quot;<td><b>&quot; & &quot;Office&quot; & &quot;</td></b>&quot; & &quot;<td>&quot; & &quot;</td>&quot; & &quot;<td><b>&quot; & &quot;Phone&quot; & &quot;</td></b>&quot;
	 Response.Write vbCrLf & &quot;</tr>&quot; & vbCrLf  	 	 
	
	
        End If
       If CurJob = arrDBData(0,I) then
	 For T = 1 to 3
	   Response.Write &quot;<td>&quot; & vbTab & &quot;</td>&quot; & &quot;<td>&quot; &  arrDBData(T, I) &  &quot;</td>&quot; 
	 Next ' T
	 Response.Write vbCrLf & &quot;</tr>&quot; & vbCrLf
	 cnt = cnt + 1
	 
	End If
	
	
Next ' I	
Response.Write &quot;<tr BGCOLOR='66FF99'>&quot; & vbCrLf
Response.Write &quot;<td><b>&quot; & &quot;Count in This Job Class :&quot; & &quot;</b></td>&quot; & vbTab & &quot;<td><b>&quot; &  cnt & &quot;</b></td>&quot;	

%>
</table>

Hope you can modify it for your use..

 

[Turkbear]

Oops ...My posting has an ( what should have been, to me, obvious) error:
'uses an array containing the jobclass and employeename' should have said:
'uses an array containing the jobclass and employeename, Office and work Phone'

 

[frogggg]

I was using a dtc grid to display the results of a recordset which contained the results of the sql statement mentioned in the first post.

Is there a way to do basically what you are doing, Turkbear, without having to make the table progromatically in a loop?

Thanks anyone for your help.