Link to home
Get AccessLog in
Avatar of smfmetro10
smfmetro10Flag for United States of America

asked on

How do I loop through multiple tables to get a distinct column name from one table with multiple rows of an associated table

Hi,
I have two tables (Files, Projects) with the following colums:
Files
(File_id, req_part_id, filename, title, description)
Projects
(req_part_id, project_name, project_description, work_plan_filename)

There can be either one or two Projects with any number of files associated with each project.
So I need to write a query that shows a "Distinct Project Name" with the associated files.

Right now it shows the project name muliptle times (based on the number of files associated with them)

I hope I'm being clear enough. I've included my code for better clarification
Thanks!
<cfquery name="qshowinfo2" datasource="Accred">
select Projects.req_part_id, Projects.project_name,Projects.project_description,Projects.work_plan_filename,Files.req_part_id,Files.title,Files.description,Files.filename
from Projects
inner join Files ON Projects.req_part_id = Files.req_part_id
</cfquery>

<cfoutput query="qshowinfo2">
#project_name#<br />
#project_description#<br />
#work_plan_filename#<br />
#filename#<br />
#title#<br />
#description#<br />
</cfoutput>

Open in new window

SOLUTION
Avatar of Rcollins207
Rcollins207
Flag of Australia image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
ASKER CERTIFIED SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of gdemaria

All you need to do is GROUP your output, this is the way to do it with a single query.  

Using the query of queries will be much slower..



Try this...

<cfoutput query="qgetworkplan" group="project_name">
<h2>#qgetworkplan.project_name#</h2>
#qgetworkplan.project_description#<br>
#qgetworkplan.work_plan_filename#<br>
  <cfoutput>
   #qgetworkplan.filename#<br/>
   #qgetworkplan.title#<br/>
   #qgetworkplan.description#<br/>
  <cfoutput>
  <hr>
</cfoutput>

Using the query of queries will be much slower..

Agreed. Running a separate query for every req_part_id is far less efficient.  Even if it is a QoQ. Using a single query as gdemaria suggested is much more efficient.  Note: be make sure to ORDER BY project_name for the <cfoutput group="project_name" ...> to work properly.
>  Even if it is a QoQ

In fact, and I'm not sure this is always the case, but QoQ is often slower than hitting the database again !

Yeah, I'm sure it varies. But I can definitely believe it's slower than a db query in many cases. Particularly when loops are involved.  From what I know, there's a lot of prep work involved in QoQ's. So what's marginally more efficient when run 1x, could easily degrade .. and end up being more expensive when run w/in a loop (10x,20x, or more)
Avatar of smfmetro10

ASKER

I solved it by using a query of queries then used a nested loop to output just the files associated with each project.

You should rethink your approach.

The best method here is using the group in the cfoutput, there is no need to perform extra queries.  

If you HAD to perform a query within the loop, it would be more efficient to query the database within the loop rather than query of queries.




You should rethink your approach.

I agree.  Re-querying on each loop is very inefficient. No matter which type of query is used, this method does NOT scale well.  A single query with <cfoutput "group"...> is the better approach.