Dude... You're using ancient syntax... Move into the 90's and use joins in the from clause :) Consider rewriting in ansi-99 syntax...
Queries that appear like the following are hard to read for most people nowadays: Select * from table1, table2 where table1.pk = table2.fk We write that today as: Select * from table1 inner join table2 on table1.pk = table2.fk I think though that left join mantioned by scott in ansi-92 is *= or =* in the from clause: Select * from table1, table2 where table1.pk *= table2.fk That takes everything from table1 and joins the table2 entries that match. -----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 ______________________________________________________________________ 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/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
