Skip to main content
E
elvideo123
Participating Frequently
February 15, 2013
Question

New to CF, not sure best output approach, missing something

  • February 15, 2013
  • 2 replies
  • 1643 views

I searched on the forum to look for something that might answer this but didn't find much so here goes.

So far so good, but I am not sure how to fix my problem here is the background.  I have two queries (stored procedures) that I use to display output.  For the sake of clarity I am going to stay away from SQL techniques like union joining or re-writing my base query.  The 2 results have what I need, it's just a question of how I combine them in a way that works, because my results aren't working.  All db connections and results return fine, so just assume that.

Ok, so first I have a query that when dumped looks like the following, it's a count list total of items for certain people, each person has a unique ID associated with them: so a simple cfoutput query makes the result look like this:

First query result (q1)

ID     Name                    Count1     Count2     Count3

1          Tom Luck               2               88               33

2          Sally Blue               9                    7               2

3          John Code               3               3                    21

4          Sam Smith               8               8                    1

etc...

The first result has about 10 names.

Now what I have is a second query that is exactly the same except the numbers are different, they are current counts of those items for the people.  They can be any number, but wont be larger than the total of the original.  Also some Names will not appear in the list because the user has no counts so the list might look something like this

Second query result (q2)

ID      Name                Count1 Count2 Count3

1           Tom Luck           2           33           3

2           Sally Blue            9             4           0

4          Sam Smith          8               3          1

etc...

So it's pretty simple to just make a <cfoutput query> tag and dump one of these table out.  the problem is I am trying to combine them.

The first query is the base for my output, because it will always contain more names.  What I am trying to do cosmeticaly is produce the following output in coldfusion.  Here is the example based on the sample data above of what it would look like:

(expected output to achieve)

ID         Name                    Count1          Count2          Count3

1               Tom Luck           2/2                33/88                33/33

2                Sally Blue           9/9                4/7                    0/2

3               John Code           0/3               0/3                    0/21

4               Sam Smith           8/8                  3/8                     1/1

For a note the IDs don't have to be shown, but that's not an issue.  So now you get the idea... now the code problem...

The cf code I wrote to do this looks like the following, but does not produce the right output..  It's close though, but I am doing something wrong.

<table style="border-style: solid; border-width: thin; font-family: Arial, Helvetica, sans-serif">

            <tr><td align="center"><b>Name</b></td><td><b>count1</b></td><td><b>count2</b></td><td><b>count3</b></td></tr>

            <cfoutput query="q1">

                    <tr><td>#q1.PersName#</td><td style="border-style: solid; border-width: thin; " align="center" >

                    <cfloop query="q2" >

                     <cfif q2.ID eq q1.ID>

                         <cfoutput>#q2.C1#</cfoutput>

                            <cfbreak> <!--- Write option and break out of loop --->

                         <cfelse>

                                  0

                        </cfif>

                    </cfloop>

                    / #q1.C1#</td>

                        <td style="border-style: solid; border-width: thin; " align="center" >/ #q1.C2#</td>

                    <td style="border-style: solid; border-width: thin; " align="center" >/ #q1.C3#</td></tr>

            </cfoutput>

</table>

I am getting a bit of redundancy because it's obviously going through the second query for each record instance of the first one producing the same count number over and over again for count1.  (for the sake of simplicity I did not re do this code for counts2 and 3 because I can't get this working correctly).

I am not sure how to code this, but I am sure the fix is real simple either by a property designation or some other way.

Any advice would be appreciated to get the right output expected above.

thanks,

This topic has been closed for replies.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
February 17, 2013

<cfset q1_name_list = valueList(q1.persName)>

<cfset q1_count1_list = valueList(q1.c1)>

<cfset q1_count2_list = valueList(q1.c2)>

<cfset q1_count3_list = valueList(q1.c3)>

<cfset q2_name_list = valueList(q2.persName)>

<cfset q2_count1_list = valueList(q2.c1)>

<cfset q2_count2_list = valueList(q2.c2)>

<cfset q2_count3_list = valueList(q2.c3)>

<table>

<th><td align="center">Name</td><td>count1</td><td>count2</td><td>count3</td></th>

<!--- Loop through list of names from first(larger) query --->

<cfloop list="#q1_name_list#" index="current_q1_name">

    <cfset listPosition1 = listFindNoCase(q1_name_list,current_q1_name)>

    <!--- Search in second list for a name from first list. Note its position --->

    <cfset listPosition2 = listFindNoCase(q2_name_list,current_q1_name)>

    <cfif listPosition2 eq 0><!--- Then name from first list not in second list --->

        <cfoutput><tr><td>#current_q1_name#</td><td>0/#listGetAt(q1_count1_list,listPosition1)#</td><td>0/#listGetAt(q1_count2_list,listPosition1)#</td><td>0/#listGetAt(q1_count3_list,listPosition1)#</td></tr></cfoutput>

    <cfelse><!--- Name from first list occurs in second list --->

        <cfoutput><tr><td>#current_q1_name#</td><td>#listGetAt(q2_count1_list,listPosition2)/#listGetAt(q1_count1_list,listPosition1)#</td><td>#listGetAt(q2_count2_list,listPosition2)#/#listGetAt(q1_count2_list,listPosition1)#</td><td>#listGetAt(q2_count3_list,listPosition2)#/#listGetAt(q1_count3_list,listPosition1)#</td></tr></cfoutput>

    </cfif>

</cfloop>

</table>

D
Dan_Bracuk
Inspiring
February 15, 2013

ColdFusion has something called query of queries that enables you to do this sort of thing with sql syntax instead of nested loops.  To implement it, use dbtype="query" in your cfquery tag.  Then you can write sql by using query names instead of table names.

There are some limitations to this but it's still very handy. 

E
elvideo123Author
Participating Frequently
February 15, 2013

looked it up, looks like in theory that could do the trick... however can you use inner joins in the SQL clause?

the following says there is a problem with my inner key word:  (assume above code)

  <cfquery dbtype="query" name="GetCA">

           SELECT Q1.PersName, Q2.Count1, Q1.Count1

           FROM Q1 INNER JOIN Q2 ON

           Q1.To_ID = Q2.To_ID;

  </cfquery>

I think that's the correct SQL syntax?

E
elvideo123Author
Participating Frequently
February 15, 2013

Actually I would need an outer, which I tried, and that doesn't work either.  I used a where claused and forced a join but you can't use a syntax like *= to get the other records.  Is the amount of SQL limited that you can write in the QoQ?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices