Populating a structure from multiple queries

Report · Aug 02, 2008

I have 3 tables of info that share a common column called "cost code". One table (CostCode) defines each cost code. For instance

(**NOTE:Rows are seperated by commas)

Column1 - (PrimaryKey) ID=1,2
Column2 - CostCode=555,444

A second table has a number of rows with tasks (Column2) with Manhours(Column3) assigned which are labeled with cost codes in the Cost Code Column (Column4). The value is a primary key value from the "CostCode" table. There are many number of task with no specific limit labeled with the same cost code value. Here is a look.

Column1 - ID=450
Column2 - Task=Build Scaffold,Work on Scaffold
Column3 - CostCode=1(555),2(444)
Column4 - Manhours=20,1
Column5 - ManhoursComplete=15,0

The third is a table of Job Numbers with the same cost codes with Dollars assigned to them. Here is a look.

Column1 - ID=1,2,3
Column2 - JobNumber=2833,3023,3023
Column3 - CostCode=555,444,555

Here is the goal:

I would like to have a query gather the data from the three tables then display them in an HTML table. Each row would be a cost code row as defined below:

<cfloop query=??>
<cfoutput>
Column1 - CostCode = #CostCode#
Column2 - TotalManhours = #(SUM(Manhours))#
Column3 - Dollars =#Dollars#
Column4 - PercentComplete = #(ManhoursComplete/Manhours)#
</cfoutput>
</cfloop>

The problem is obvioulsy, I can only loop one query. I have also tried to build a structure, which I am not very experienced with, to hold all the data and I was pretty close to doing so but could figure out how to loop the data (manhours) into an array. I used an index loop but the out put displayed only one value (first value in the loop) throughout the sequence of array positions numbering the record count.

<cfset ManhoursArray=ArrayNew(1)>
<CFSET TotalRows = TrackingDesc.RecordCount>


<cfloop query="TrackingDesc">
<cfloop index="Manhours" from=1 to="#TotalRows#">
<cfset ManhoursArray[Manhours]="#Manhours#">
</cfloop></cfloop>

I am up for suggestions and hope I didn't confuse anyone. What would be the best way to display all of these results. NOTE: The database tables have to be structure like this due to large amounts of other data columns in them.

Report · Aug 02, 2008

If you don't know how to get all the data you need with just one query, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

Report · Aug 02, 2008

if you have never heard of INNER/OUTER JOINs (joining several tables in
one query), then get yourself an sql book.
Ben Forta's 'Teach yourself SQL in 10 minutes' comes highly recommended
by many people.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/