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
