Basically your saying SELECT table1 and JOIN it with table2 using ON as the condition
so yeah thats right what you have done now Steve -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Mark Stanton Sent: Friday, February 13, 2004 12:01 PM To: CFAussie Mailing List Subject: [cfaussie] RE: MS SQL problem Steve - a million thanks - you are a genious. James & I never put it together that: FROM species, tree LEFT OUTER JOIN region_tree ON tree.treeUUID = region_tree.treeUUID was the right way to do it - didn't think the order of tables in the FROM clause made a difference. Cheers Mark ------------------ Mark Stanton Technical Director Gruden Pty Ltd Tel: 9956 6388 Mob: 0410 458 201 Fax: 9956 8433 http://www.gruden.com ******************************************************************** This message and any attachments are confidential to the ordinary user of the e-mail address to which it was addressed and may also be privileged. If you are not the addressee you may not copy, forward, disclose or use any part of the message or its attachments and if you have received this message in error, please notify the sender immediately by return e-mail and delete it from your system. Internet communications cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, arrive late or contain viruses. The sender therefore does not accept liability for any errors or omissions in the context of this message which arise as a result of Internet transmission. Before opening or using attachments, check them for viruses and defects. Our liability is limited to resupplying any affected attachments. ******************************************************************** -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Onnis Sent: Friday, 13 February 2004 10:52 AM To: CFAussie Mailing List Subject: [cfaussie] RE: MS SQL problem Mark FROM tree, species LEFT OUTER JOIN region_tree ON tree.treeUUID = region_tree.treeUUID Your joining species and region_tree is that right? you can only join tables in your ON clause that you define in your JOIN clause Steve -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Mark Stanton Sent: Thursday, February 12, 2004 7:42 PM To: CFAussie Mailing List Subject: [cfaussie] MS SQL problem Hey All Been beating my head against this one for a while now..... I've got two queries that work and a third (which is the one I am really after) that doesn't. --- Query 1 - get trees & their species - works fine --- SELECT tree.treeUUID, tree.treeName, species.speciesDesc FROM tree, species WHERE species.speciesUUID = tree.speciesUUID --- Query 2 - get trees & their regions (if defined) - works fine --- SELECT tree.treeUUID, tree.treeName, region_tree.regionUUID FROM tree LEFT OUTER JOIN region_tree ON tree.treeUUID = region_tree.treeUUID GROUP BY tree.treeUUID, tree.treeName, region_tree.regionUUID --- Query 3 - combination of the above - broken --- SELECT tree.treeUUID, tree.treeName, region_tree.regionUUID, species.speciesDesc FROM tree, species LEFT OUTER JOIN region_tree ON tree.treeUUID = region_tree.treeUUID WHERE species.speciesUUID = tree.speciesUUID GROUP BY tree.treeUUID, tree.treeName, region_tree.regionUUID, species.speciesDesc The error I get is: Server: Msg 107, Level 16, State 3, Line 1 The column prefix 'tree' does not match with a table name or alias name used in the query. Any ideas? Cheers Mark ------------------ Mark Stanton Technical Director Gruden Pty Ltd Tel: 9956 6388 Mob: 0410 458 201 Fax: 9956 8433 http://www.gruden.com --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
