RE: UNION vs JOIN
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 __ Get the mailserver that powers this list at http://www.coolfusion.com 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
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 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
RE: UNION vs JOIN
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 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
RE: UNION vs JOIN
If you have a single table with fields from other tables you can join them all fairly simply. Consider the following four tables: cars carid cartypeid carcolorid carmodelid carmodel carmodelid model carcolor carcolorid color cartype cartypeid type You can get the data from all the tables with a join: (i'm using the older syntax cause it is easier, the letters are aliases) select a.carid, b.model, c.color, d.type from cars a, carmodel b, carcolor c, cartype d where a.carmodelid = b.carmodelid and a.carcolorid = c.carmodelid and a.cartypeid = d.cartypeid and a.carid = That will get all the data for one car. Good Fortune, Richard Walters, Webmaster, Davita Laboratory Services [EMAIL PROTECTED] (800) 604-5227 x 3525 [EMAIL PROTECTED] 04/18/02 11:14AM 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 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
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 12 department 23 product 24 product 25 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 __ Get the mailserver that powers this list at http://www.coolfusion.com 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
RE: UNION vs JOIN
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 12 department 23 product 24 product 25 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
Re: UNION vs JOIN
Yup, that is what I am currently doing ... 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 12 department 23 product 24 product 25 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 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
