MySQL error, worked in v4?

Zeno McDohl · 05-15-2009, 03:01 PM

Does this select look wrong?

Code:

mysql> SELECT u.id as editid, u.*, c.* , e.* from mos_users as u, mos_user_extended_config as c LEFT JOIN mos_user_extended AS e ON u.id = e.id WHERE u.id = '72' AND c.id = '1';
ERROR 1054 (42S22): Unknown column 'u.id' in 'on clause'

I'm a bit confused why it errors out if this works:

Code:

mysql> SELECT u.id as editid FROM mos_users as u LIMIT 1;
+--------+
| editid |
+--------+
|     62 |
+--------+
1 row in set (0.01 sec)

I don't know for sure, but I think it worked when we were using MySQL 4.x, now we're using 5.x.

estabroo · 05-15-2009, 06:06 PM

didn't you tell it call u.id editid, so maybe you need to change the last part to ON editid = e.id WHERE editid = '72' ...

Zeno McDohl · 05-21-2009, 12:57 PM

Quote:

mysql> SELECT u.id as editid, u.*, c.* , e.* from mos_users as u, mos_user_extended_config as c LEFT JOIN mos_user_extended AS e ON editid = e.id WHERE editid = '72' AND c.id = '1';
ERROR 1054 (42S22): Unknown column 'editid' in 'where clause'

Doesn't seem to work.

soleilarw · 05-22-2009, 09:22 AM

This part looks wrong to me:

Quote:

mysql> SELECT u.id as editid, u.*, c.* , e.* from mos_users as u, ...

I would try
mysql> SELECT u.id as editid, u.*, c.* , e.* as u from mos_users, ...

Linux Archive

Zeno McDohl · 05-22-2009, 10:52 AM

That doesn't work either.

Quote:

mysql> SELECT u.id as editid, u.*, c.* , e.* as u from mos_users, mos_user_extended_config as c LEFT JOIN mos_user_extended AS e ON u.id = e.id WHERE u.id = '72' AND c.id = '1';
ERROR 1051 (42S02): Unknown table 'u'

graemef · 05-22-2009, 10:16 PM

I would suggest that you get it working without the abbreviations and then add them since it looks as if your problem is with the abbreviations but maybe it is with something else.

norobro · 05-23-2009, 10:54 AM

Isnt this your problem?

From the MYSQL 5.0 reference manual:

Quote:

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Looks like you are right about it working previously.

Zeno McDohl · 06-08-2009, 12:13 PM

Hm that looks like the issue. Testing now.

Quote:

SELECT u.id as editid, u.*, c.* , e.* from (mos_users as u, mos_user_extended_config as c) LEFT JOIN mos_user_extended AS e ON (u.id = e.id) WHERE u.id = '72' AND c.id = '1';

Works