Webmaster Forum / ColdFusion / General CF Topics / March 2008

Thank Azadi for showing me the way to do it! I got basic SQL JOIN knowledge and
just verified them in http://www.w3schools.com/sql/default.asp. For example:

SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

In this above query, having same fieldname: Employee_ID in two table Employees
and Orders in order to INNER JOIN.

In my post, my tables never had same fieldname. Ian said
FROM aTable INNER JOIN bTable ON aTable.george = bTable.gracie

I am not sure why he used george and gracie? are these the column name or
value???. Azadi was right that I need to build a JOIN on two different name
columns which have some same values. Anyway, ve learn and broaden my knowledge.
Tthank for all your help.  
 

Look in my post, any two different tables never have the same column
fieldname.

I never see an example that INNER JOIN on different column name that Ian said
in his post.

Hello Azadi,

I have applied your instruction to build the code as following:
========================================================
<cfquery name="get_level_list" datasource="#primarydatasource#">
  select mail_split_code,sort_level, label_list
  from z_ct_sortation_t
  where mail_split_code in (select distinct mail_stream_split_ind
                           from uff_dtl_in_vw
                           where electronic_file_id = '#ElectronicFileID#')
                                        and label_list <> 'Mailpiece'
                                        order by sort_level
</cfquery>
             
<cfoutput query = "get_level_list">
     <cfloop>
      <cfquery name="get_labellist" datasource="#primarydatasource#">
        select label_list_name, zip_column_name from z_labellist_t
        where label_list_name = #get_level_list.label_list#
      </cfquery>
          <cfloop>
               <cfquery name="get_city_st" datasource="#primarydatasource#">
               select label_dest_zip_code,label_id,state_abbr
               from #get_labellist.label_list_name#_t
               where
                   <cfif #get_labellist.zip_column_name# eq
"three_dig_zip_code">
       three_dig_zip_code = #Mid(entry_zip_code,1,3)#
      <cfelse>                    
                   five_dig_zip_code = #entry_zip_code#
    </cfif>  
               </cfquery>
               <table>
    <tr>
                    <th>CITY</th>
                    <th>STATE</th>
                    <th>DEST ZIP</th>
    </tr>  
    <tr>
                    <td>#label_dest_zip_code#</td>
                    <td>#label_id#</td>
                    <td>#state_abbr#</td>
    </tr>
               </table>  
             </cfloop>
             </cfloop>
            </cfoutput>
=====================================================
I got the error at <cfloop> when I execute the code as belowed

Error Occurred While Processing Request  
Attribute validation error for tag CFLOOP.  
The tag has an invalid attribute combination: . Possible combinations are:
Required attributes: 'from,index,to'. Optional attributes: 'step'.
Required attributes: 'condition'. Optional attributes: None.
Required attributes: 'query'. Optional attributes: 'endrow,startrow'.
Required attributes: 'index,list'. Optional attributes: 'delimiters'.
Required attributes: 'collection,item'. Optional attributes: None.
 
 
The error occurred in report.cfm: line 378
 
376 :              
377 :              <cfoutput query = "get_level_list">
378 :              <cfloop>
379 :              <cfquery name="get_labellist"
datasource="#primarydatasource#">
380 :              select label_list_name, zip_column_name from z_labellist_t

Please take a look on my code and let me some more advices. Thanks