James,

Both ...

SELECT ...
FROM a, b LEFT JOIN c ON a.x=c.y

and

SELECT ...
FROM a
LEFT JOIN B USING (x )
LEFT JOIN c ON a.x=c.y

work up to and including version 5.0.10, not in 5.0.11, 12 or 13.

http://bugs.mysql.com/bug.php?id=13832 reports...

"The two statements below are quite different from one another:
1) SELECT * FROM t1, t2 LEFT JOIN t3 ON t1.a=t3.c
2) SELECT * FROM t1 JOIN t2 LEFT JOIN t3 ON t1.a=t3.c

"Statement (1) above will likely continue to give an "Unknown column 't1.a' in
'on clause'" error, while statement (2) will likely function correctly at some
point in the future.

"This bug report is in reference to statements like statement (2), and no bug
reports that use a statement like statement (1) are duplicates of this bug."

The behaviour of (1) above is also verified (http://bugs.mysql.com/bug.php?id=13551), and that page explains...

"This is a change that was made in 5.0.15 [sic] to make MySQL more compliant with the standard. According to the SQL:2003

<from clause> ::= FROM <table reference list>
<table reference list> ::=
<table reference> [ { <comma> <table reference> }... ]
<table reference> ::=
<table factor>
| <joined table>
<joined table> ::=
<cross join>
| <qualified join>
| <natural join>
...

"Thus when you write

... FROM t1 , t2 LEFT JOIN t3 ON (expr)

it is parsed as

(1) ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))

and not as

(2) ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)

so, from expr you can only refer to columns of t2 and t3 - operands of the join. Workaround - to put parentheses explicitly as in (2). Then you can refer to t1 columns from expr.

Unfortunately, this change is not properly documented in the manual, it will be fixed."

PB
http://www.artfulsoftware.com

-----

SGreen@unimin.com wrote:

James Black <jblack@ieee.org> wrote on 10/12/2005 02:06:26 PM:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Peter Brawley wrote:

James,

You can reproduce that error by writing ...

  SELECT ...
  FROM a, b INNER JOIN c ON a.x=c.y

The error goes away if you instead write ...

  SELECT ...
  FROM b, a INNER JOIN c ON A.x=c.y

so you might try swapping

FROM items i
, nams.netids n

  Tried that,now I get:
Unknown column 'n.badge'in 'on clause'

  So, whichever order I put them in, I get one of two errors.

  It appears that this bug will continue to break for me until it is
fixed in the next version, hopefully.

- --
"Love is mutual self-giving that ends in self-recovery." Fulton Sheen
James Black    james@usf.edu
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDTVCiikQgpVn8xrARAqGjAJ9y4/ym15QPZj8KVvbyuIkmTIKMKACfTlP9
cd7w92nB8uhOH2Y1+jAe4MU=
=FOcN
-----END PGP SIGNATURE-----



What if, instead of using a comma, you use an explicit INNER JOIN? It's 
perfectly valid to leave out the ON clause of an INNER JOIN (creating a 
Cartesian product).  I mention this because you don't seem to have a term 
to use in an ON clause, unless you want to move the term n.netid='jblack' 
from the WHERE clause. 

SELECT...
FROM items i
INNER JOIN nams.netids n
INNER JOIN ...
...

Does the problem remain? If it goes away, this would be useful information 
to include in your bug report.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date: 10/12/2005