Costas, > > ONE BranchBusinessNumbers TO MANY QuoteCalculator > ONE BranchBusinessNumbers TO MANY BranchTaxRelationship >
Is actually: > > ONE QuoteCalculatorTO MANY BranchBusinessNumbers > ONE BranchBusinessNumbers TO MANY BranchTaxRelationship > AND > Furthermore, you need to ensure that the QuoteCalculatorVariable table has > ONE TO ONE relationships with quotecalculator table and tax relationship > table. is actually: ONE quotecalculator TO MANY QuoteCalculatorVariable I've temporarily done the bad approach...run query with joins...if no recordcount...run again without joins (essentially what this means is that if the query with joins has no results then there are no taxes that apply). A little extra CF logic clens things up ;-) Thanks for the help Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] --------------------------------------------------------- Macromedia Associate Partner www.macromedia.com --------------------------------------------------------- Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com ----- Original Message ----- From: "Costas Piliotis" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, June 27, 2002 5:26 PM Subject: RE: Crosspost: SQL JOINS - brain fart > I think you're right Bryan. After dumping your complex scheme into visio, > it appears as though you have a circular relationship happening. I've > removed the underscores because underscores really suck: > > QuoteCalculator -> QuoteCalculatorVariables -> BranchTaxRelationship -> > BranchBusinessBumbers -> QuoteCalculator > > From what I understand after taking 10 minutes to draw out your schema is > the following: > > ONE BranchBusinessNumbers TO MANY QuoteCalculator > ONE BranchBusinessNumbers TO MANY BranchTaxRelationship > > Is that correct? If that is the case, you need to reduce at least one, if > not both to ONE TO ONE relationships by deriving tables and then joining > them in. > > Furthermore, you need to ensure that the QuoteCalculatorVariable table has > ONE TO ONE relationships with quotecalculator table and tax relationship > table. > > A union query isn't necessary. You can summarize data and then join it in. > This is a flaw in the schema that you can't avoid now unfortunately. > > > -----Original Message----- > From: Bryan Stevenson [mailto:[EMAIL PROTECTED]] > Sent: Thursday, June 27, 2002 3:59 PM > To: CF-Talk > Subject: Re: Crosspost: SQL JOINS - brain fart > > > Yes I tried LEFT JOINs with no success. I think what I need is a nested > LEFT JOIN or a UNION of 2 LEFT JOINS. > > The problem that I had was getting 30 records instead of 2 and the 30 were > the same 2 records repeated over and over (Cartesian product I beleive). > Each record also had data from the Sup_Branch_Tax_Relationship table because > of the LEFT Join by Cost_Type_ID (That table does NOT have any records that > match by Branch_ID, but it does have records that match by Cost_Type_ID....I > need there to be a match for BOTH). > > Hope that helps ;-) > > Bryan Stevenson B.Comm. > VP & Director of E-Commerce Development > Electric Edge Systems Group Inc. > t. 250.920.8830 > e. [EMAIL PROTECTED] > > --------------------------------------------------------- > Macromedia Associate Partner > www.macromedia.com > --------------------------------------------------------- > Vancouver Island ColdFusion Users Group > Founder & Director > www.cfug-vancouverisland.com > > ----- Original Message ----- > From: "Van Vliet, Scott" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, June 27, 2002 3:40 PM > Subject: RE: Crosspost: SQL JOINS - brain fart > > > > Did you try LEFT JOIN? > > > > -- > > Scott Van Vliet > > Sempra Energy > > 555 W. 5th St., 21st Floor > > Los Angeles, CA 90013 > > Tel > 213.244.5205 > > Email > [EMAIL PROTECTED] > > > > "Hello Stupid, and welcome to your crappy computer." > > - Strong Bad, HomestarRunner.com > > > > > > > > > > > > > > > -----Original Message----- > > > From: Bryan Stevenson [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, June 27, 2002 3:03 PM > > > To: CF-Talk > > > Subject: Crosspost: SQL JOINS - brain fart > > > > > > > > > OK...I'm trying to pull records from the > > > "Sup_Quote_Calculator_Stored_Variable" table wether or not any > > > records exist in these 2 tables (Sup_Branch_Tax_Relationship and > > > Sup_Branch_Business_Numbers). > > > > > > MS SQL Server so this is T-SQL > > > > > > Relationships: > > > Sup_Quote_Calculator linked to Sup_Quote_Calculator_Stored_Variable > > > via Sup_Calculator_ID Sup_Quote_Calculator linked to > > > Sup_Branch_Business_Numbers via Branch_ID > > > Sup_Branch_Business_Numbers linked to > > > Sup_Branch_Tax_Relationship via Branch_ID > > > Sup_Quote_Calculator_Stored_Variable linked to > > > Sup_Branch_Tax_Relationship via Cost_Type_ID > > > > > > I tied using a bunch of OUTER joins in the FROM clause and it > > > returned too many records because my joining sucked, so below is the > > > original query (which works fine if there are records in all > > > tables). HELP! > > > > > > SELECT > > > Sup_Quote_Calculator_Stored_Variable.*, > > > Sup_Branch_Tax_Relationship.*, > > > Sup_Branch_Business_Numbers.Federal_Rate, > > > Sup_Branch_Business_Numbers.Provincial_Rate, > > > Sup_Branch_Business_Numbers.District_Rate, > > > Sup_Branch_Business_Numbers.County_Rate, > > > Sup_Branch_Business_Numbers.City_Rate > > > FROM > > > Sup_Quote_Calculator, > > > Sup_Quote_Calculator_Stored_Variable, > > > Sup_Branch_Tax_Relationship, > > > Sup_Branch_Business_Numbers > > > WHERE > > > > > > Sup_Quote_Calculator_Stored_Variable.Sup_Calculator_ID = > > > <cfqueryparam value="#Attributes.Calculator_ID#" > cfsqltype="cf_sql_integer"> > > > AND > > > Sup_Quote_Calculator_Stored_Variable.Sup_Calculator_ID = > > > Sup_Quote_Calculator.Sup_Calculator_ID > > > AND > > > Sup_Quote_Calculator.Branch_ID = > > > Sup_Branch_Tax_Relationship.Branch_ID > > > AND > > > Sup_Branch_Tax_Relationship.Branch_ID = > > > Sup_Branch_Business_Numbers.Branch_ID > > > AND > > > Sup_Quote_Calculator_Stored_Variable.Cost_Type_ID = > > > Sup_Branch_Tax_Relationship.Cost_Type_ID > > > AND > > > Sup_Quote_Calculator_Stored_Variable.Cost_Type_ID IS NOT > > > NULL > > > > > > Thanks in advance > > > > > > Bryan Stevenson B.Comm. > > > VP & Director of E-Commerce Development > > > Electric Edge Systems Group Inc. > > > t. 250.920.8830 > > > e. [EMAIL PROTECTED] > > > > > > --------------------------------------------------------- > > > Macromedia Associate Partner > > > www.macromedia.com > > > --------------------------------------------------------- > > > Vancouver Island ColdFusion Users Group > > > Founder & Director > > > www.cfug-vancouverisland.com > > > > > > > > > > ______________________________________________________________________ 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/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
