ah. <please insert my comments about data design here>. While they are not the issue, it may be work reexamining how you have your tables constructed. for example, looks like you can fold the three "detail" tables into one.
if we consider that this layout is a priori, and the scenario is as outlined below, then you're probably looking for SELECT template FROM department_table WHERE ID = #URL.owner# UNION SELECT template FROM product_table WHERE ID = #URL.owner# UNION SELECT template FROM content_table WHERE ID = #URL.owner# this is off the top of my head. should be fairly close, though. christopher olive cto, vp of web development, vp it security atnet solutions, inc. 410.931.4092 http://www.atnetsolutions.com -----Original Message----- From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 11:39 AM To: CF-Talk Subject: Re: UNION vs JOIN Ok, here it is: fusion_table id | owner | sub_object | type | sort | status department_table id | title | description | content | status | TEMPLATE product_table id | title | description | content | status | TEMPLATE .... various other columns content_table id | title | description | content | status | TEMPLATE The fusion table combines the id values from the other three tables, in the two columns OWNER and SUB_OBJECT. All owners can have multiple sub_objects and sub_objects can become owners. The type field is one of three values department, product, or content So if I have id's 1 = department 2 = department 3 = product 4 = product 5 = content and my fusion table looks like this: owner | sub_object | type 1 2 department 2 3 product 2 4 product 2 5 content Owner 1 has the sub_object 2 (sub_department) owner 2 has the sub objects 3,4 (products) and 5(content item). So if I have a link loading up ID=4 the product and want to view it's records, "go.cfm?owner=4" I want to write a query that will get the template out of the appropriate table ... I do not want to do a <cfif type Is "product"> run this query <cfelseif type Is "department"> run this query <cfelse> run this query </cfif> I would like to run a query that will "Join or Union" the tables to get the TEMPLATE so I can tell which template to use. I would rather not duplicate the data and keep the template attached to each item. Paul Giesenhagen QuillDesign http://www.quilldesign.com SiteDirector v2.0 - Commerce Builder > can you give more detail? from just this amount, it sounds like a join may be in the making. are all the tables related via the relationship table? > > christopher olive > cto, vp of web development, vp it security > atnet solutions, inc. > 410.931.4092 > http://www.atnetsolutions.com > > > -----Original Message----- > From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] > Sent: Thursday, April 18, 2002 11:00 AM > To: CF-Talk > Subject: Re: UNION vs JOIN > > > I am combining the results of three tables that are very similar in their > setup and the fourth table holds all the relationships. > > Paul Giesenhagen > QuillDesign > http://www.quilldesign.com > SiteDirector v2.0 - Commerce Builder > > > > they do different things, really. what are you trying to accomplish? > > > > christopher olive > > cto, vp of web development, vp it security > > atnet solutions, inc. > > 410.931.4092 > > http://www.atnetsolutions.com > > > > > > -----Original Message----- > > From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, April 18, 2002 10:18 AM > > To: CF-Talk > > Subject: OT: UNION vs JOIN > > > > > > Is there a significant difference in performance using a UNION on four > > tables versus a JOIN on four tables? > > > > Thanks > > > > Paul Giesenhagen > > QuillDesign > > http://www.quilldesign.com > > SiteDirector v2.0 - Commerce Builder > > > > > > > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
