The best way to be sure you are getting the most accurate data is to specify the columns you want to return in the SELECT clause, rather than using SELECT *. This also helps ensure that if you should add column to a table down the road it doesn't break a query with a join that had been working.
In addition, you should see better performance with specific columns specified, as the query will not return a lot of data that may be unnecessary. -----Original Message----- From: Brian Scandale [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 20, 2002 3:07 AM To: CF-Talk Subject: sql and alias names I have a query that is giving me a bit of a problem. <cfquery datasource="#request.theDSN#" name="theHistory"> SELECT * FROM History LEFT OUTER JOIN Op ON History.Op_ID = Op.Op_ID LEFT OUTER JOIN TravelerD ON History.TravelerD_ID = TravelerD.TravelerD_ID LEFT OUTER JOIN Notes ON History.History_ID = Notes.History_ID WHERE History.Device_ID = #theLot.Device_ID# ORDER by History.Date_Out </cfquery> TravelerD and Notes both share a field named TravelerD_ID. When there is not a matching Notes record, theHistory.TravelerD_ID from the query field ends up empty even though there is a TravelerD record that matches the query... the 2nd left outer join relates to it. How does one query tables that share common field names in this case. I can't seem to work out the alias technique here. As an aside.... history.op_id ALWAYS has a match in op history.travelerd_id does NOT always have a match in travelerd history.history_id does NOT always have a match in notes am I using the Left Outer Join properly here? thanks, Brian ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.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
