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 > > > > > ______________________________________________________________________ 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/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
