Re: UNION vs JOIN
Yup, that is what I am currently doing ... > ah. > > . 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 > > run this query > > run this query > > run this query > > > 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
RE: UNION vs JOIN
ah. . 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 run this query run this query run this query 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
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 run this query run this query run this query 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
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
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
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
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: OT: UNION vs JOIN
Union Adds the rows from a second table, Join combines the tables. It's conceivable that they produce the same result if you're using an outer join, but they're different. Good Fortune, Richard Walters, Webmaster, Davita Laboratory Services [EMAIL PROTECTED] (800) 604-5227 x 3525 >>> [EMAIL PROTECTED] 04/18/02 10:18AM >>> 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
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
