Hello forum I am having a little problem with nested query loops that I figured I would turn to the forum to ask for help on. I have been racking my brain trying to figure this one out and drawing a blank. So the situation is I have two queries one for an outer loop and the other for an inner loop. The outer loop gets information about a project or job that has been inserted into the Database previously by the owner and the other get information about the user that has placed a bid on the same project or job. When everything works like it supposed to there should be a table header row with the project details and beneath the header should be a row with people who placed a bid on that particular project or job.
The outer loop works fine the inner loop is where I am having the issue. Its basically repeating the same row under each column in the second loop which makes sense as to why it would do that but not getting the results as intended. Below is the Html I am using:
<cfif gJobsCreated.recordCount>
<table id="mytable" cellspacing="0" summary="The technical specifications of the Apple PowerMac G5 series">
<cfoutput query="gJobsCreated" group="jobid">
<tr>
<th scope="col" abbr="Configurations" class="startBox">#title#</th>
<th scope="col" abbr="Dual 2GHz">#Type#</th>
<th scope="col" abbr="Dual 2GHz">#DateFormat(StartDate,'mm/dd/yyy')#</th>
<th scope="col" abbr="Dual 2GHz">#DateFormat(endDate,'mm/dd/yyy')#</th>
<th scope="col" abbr="Dual 2GHz"><cfif Budget NEQ ''>#DollarFormat(budget)#<cfelseIf Hourly NEQ ''>#DollarFormat(hourly)#/hr<cfelse>#dollarFormat(Fixed)#</cfif></th>
<th scope="col" abbr="Dual 2GHz"><a href = "?app=job&view=details&projectID=#JobID#">View Job</a></th>
<th scope="col" abbr="Dual 2GHz">Award Project</th>
</tr>
<cfif guserBids.recordCount>
<cfloop query="guserBids">
<tr>
<th scope="row" class="spec"></th>
<td>#dollarformat(price)#</td>
<td ></td>
<td >#iif(Session.subscript EQ 'Free',DE('#Left(email, 5)#...'),de('#email#'))#</td>
<td >
<cfif Round(rating)is 0><img src="#application.path.img#/star-zero.png">
<cfelseif Round(rating)is 1 ><img src="#application.path.img#/starrating1.png">
<cfelseif Round(rating)is 2 ><img src="#application.path.img#/starrating2.png">
<cfelseif Round(rating)is 3 ><img src="#application.path.img#/starrating3.png">
<cfelseif Round(rating)is 4 ><img src="#application.path.img#/starrating4.png">
<cfelseif Round(rating)is 5 ><img src="#application.path.img#/starrating5.png">
</cfif>
</td>
<td ><a href = "javascript:void(0);">View Profile</a</td>
<td ><a href = "##" title = "Click here to award this project" onclick = "return confirm('Are you sure you want to award this project to this user?')">Award</a></td>
</tr>
</cfloop>
<cfelse>
<table id="mytable" cellspacing="0" summary="The technical specifications of the Apple PowerMac G5 series">
<tr>
<th scope="row" align="center" style="color:red" class="empty">NO RECORDS AVAILABLE!</th>
</tr>
</table>
</cfif>
</cfoutput>
</table>
</cfif>
Am I going about this the wrong way???
here are the queries as well:
Projects Created:
SELECT j.JobID, j.City, j.stateID, j.zipcode, j.countryID, j.dateCreated, j.Jobdescription, j.certificationNeeded, j.skillNeeded, j.languagePref, j.experienceNeeded, j.type, j.budget,
j.hourly, j.fixed, j.startDate, j.endDate, a.FullTime, a.partTime, a.AsNeeded, a.WeekendsOnly, a.Contract, jc.title, js.name, s.stateName, c.countryName
FROM tbl_job AS j INNER JOIN
tbl_availability AS a ON j.availabilityID = a.availabilityID INNER JOIN
tbl_job_category AS jc ON j.categoryID = jc.categoryID INNER JOIN
tbl_job_status AS js ON j.statusID = js.statusID INNER JOIN
tbl_state AS s ON j.stateID = s.stateID INNER JOIN
tbl_country AS c ON j.countryID = c.countryID
WHERE j.userID = ?
Bids Created:
SELECT jb.bidID, jb.price, jb.description, jb.dateCreated, jb.dateUpdated, jb.isAccepted, jb.isActive, jb.isArchived, up.firstName, up.lastName,
up.firstName + ' ' + up.lastName AS FullName, jb.userID, up.address1, up.address2, up.phone1, up.phone2, up.city, up.zip, up.stateID, up.countryID, up.license,
up.UrlFacebook, up.profileimage, up.ServiceDesc, up.views, j.userID AS ProjectOwnerID, up.bestContact, up.email, up.title, j.jobID, j.isActive AS JobIsActive,
j.isComplete, COALESCE (B.TotalVotes, 0) AS Votes, CAST(COALESCE (B.totalRate, 0) AS Real) AS Rating, ISNULL(up.views, 0) AS Views, COALESCE (p.totalCost, 0)
AS Total
FROM tbl_job_bid AS jb INNER JOIN
tbl_user_profile AS up ON jb.userID = up.userID INNER JOIN
tbl_state AS s ON up.stateID = s.stateID INNER JOIN
tbl_country AS c ON up.countryID = c.countryID INNER JOIN
tbl_job AS j ON jb.jobID = j.jobID LEFT OUTER JOIN
(SELECT feedbackID, jobID, profileID, SUM(total_votes) AS TotalVotes, SUM(professionalism + performance + completion + timeliness + overall)
/ CONVERT(decimal(8, 4), COUNT(total_votes)) / 5 AS totalRate
FROM tbl_user_rate AS UR
GROUP BY jobID, feedbackID, profileID, total_votes) AS B ON B.jobID = jb.jobID LEFT OUTER JOIN
(SELECT b.profileID, SUM(a.price) AS totalCost
FROM tbl_job_invoice_charge AS a LEFT OUTER JOIN
tbl_job_invoice AS b ON a.invoiceID = b.invoiceiD
GROUP BY b.profileID, a.price) AS p ON p.profileID = up.profileID
WHERE (j.userID = ?)
AND (j.isActive = ?)