Now's here's a new problem I haven't encountered.

How do I use a url variable to link to a property details page
when the properties are in 8 different tables?  The issue really isn't
the variable, since I can use the aliased variable "property_id" (once I add it 
to the query).

I know I could use
property_details.cfm?property_id=<cfoutput>#get_properties.property_id#</cfoutput>,
however, on the details page, I would need to search all eight property tables
for the correct property id.

Is this the method I'm stuck with because all the properties aren't in a single 
table?
Pass the aliased variable "property_id" and search all 8 tables for it?

Perhaps I should just put them all into one big property table instead of 
keeping them
separate as the data vender has them...

Thoughts?

Thanks,

Rick

Here's the current state of my "union all" query with all the property tables 
finally in play:

<cfquery name="get_properties" datasource="#dsn#">
                                                
    select sa.street_number as prop_street_number, sa.street_name as 
prop_street_name,
           sa.city as prop_city, sa.public_remarks_01 as prop_public_remarks_01,
           sa.public_remarks_02 as prop_public_remarks_02, sa.list_price as 
prop_list_price,
           (select sap.photo_filename from smlc_acr_photos sap where 
sap.photo_mls_number =
sa.mls_number limit 1) as prop_photo_filename,
           (select so.office_name from smlc_off so where so.office_code = 
sa.listing_office) as
prop_listing_office
      from smlc_acr sa
 union all   
    select sc.street_number as prop_street_number, sc.street_name as 
prop_street_name,
           sc.city as prop_city, sc.public_remarks_01 as prop_public_remarks_01,
           sc.public_remarks_02 as prop_public_remarks_02, sc.list_price as 
prop_list_price,
           (select scp.photo_filename from smlc_com_photos scp where 
scp.photo_mls_number =
sc.mls_number limit 1) as prop_photo_filename,
           (select so.office_name from smlc_off so where so.office_code = 
sc.listing_office) as
prop_listing_office                                
      from smlc_com sc
 union all
    select sl.street_number as prop_street_number, sl.street_name as 
prop_street_name,
           sl.city as prop_city, sl.public_remarks_01 as prop_public_remarks_01,
           sl.public_remarks_02 as prop_public_remarks_02, sl.list_price as 
prop_list_price,
           (select slp.photo_filename from smlc_lot_photos slp where 
slp.photo_mls_number =
sl.mls_number limit 1) as prop_photo_filename,
           (select so.office_name from smlc_off so where so.office_code = 
sl.listing_office) as
prop_listing_office
      from smlc_lots sl
 union all 
    select sr.street_number as prop_street_number, sr.street_name as 
prop_street_name,
           sr.city as prop_city, sr.public_remarks_01 as prop_public_remarks_01,
           sr.public_remarks_02 as prop_public_remarks_02, sr.list_price as 
prop_list_price,
           (select srp.photo_filename from smlc_ren_photos srp where 
srp.photo_mls_number =
sr.mls_number limit 1) as prop_photo_filename,
           (select so.office_name from smlc_off so where so.office_code = 
sr.listing_office) as
prop_listing_office
      from smlc_ren sr
 union all
    select sres.street_number as prop_street_number, sres.street_name as 
prop_street_name,
           sres.city as prop_city, sres.public_remarks_01 as 
prop_public_remarks_01,
           sres.public_remarks_02 as prop_public_remarks_02, sres.list_price as 
prop_list_price,
           (select sresp.photo_filename from smlc_res_photos sresp where 
sresp.photo_mls_number =
sres.mls_number limit 1) as prop_photo_filename,
           (select so.office_name from smlc_off so where so.office_code = 
sres.listing_office) as
prop_listing_office
      from smlc_res sres
 union all 
    select smul.street_number as prop_street_number, smul.street_name as 
prop_street_name,
           smul.city as prop_city, smul.public_remarks_01 as 
prop_public_remarks_01,
           smul.public_remarks_02 as prop_public_remarks_02, smul.list_price as 
prop_list_price,
           (select smulp.photo_filename from smlc_mul_photos smulp where 
smulp.photo_mls_number =
smul.mls_number limit 1) as prop_photo_filename,
           (select so.office_name from smlc_off so where so.office_code = 
smul.listing_office) as
prop_listing_office
      from smlc_mul smul
                 
</cfquery>




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304124
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Reply via email to