in MySQL 5.0.15
u must do like this:
SELECT f.name,
f.type, f.access_level_r, f.default_value, f.type,
s.value
FROM mantis_custom_field_project_table AS p
LEFT JOIN mantis_custom_field_table AS f
ON p.field_id = f.id
LEFT JOIN mantis_custom_field_string_table AS s
ON p.field_id=s.field_id AND s.bug_id='1476'
WHERE p.project_id = '10'
ORDER BY p.sequence ASC, f.name ASC
----- Original Message -----
From: "Florin Ochiana" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, November 10, 2005 2:15 PM
Subject: Problem with joins in MySQL 5.0.15
Hi all,
I get a very strange error from a join in Mantis and this started after I
upgraded from Mysql4 to Mysql5. I know there were some changes regarding
the
joins but this error doesn't say anything about the join.
The select is this:
SELECT f.name,
f.type, f.access_level_r, f.default_value, f.type,
s.value
FROM mantis_custom_field_project_table AS p,
mantis_custom_field_table AS f
LEFT JOIN mantis_custom_field_string_table AS s
ON p.field_id=s.field_id AND s.bug_id='1476'
WHERE p.project_id = '10' AND p.field_id = f.id
ORDER BY p.sequence ASC, f.name ASC
And the error I get is
ERROR 1054 (42S22): Unknown column 'p.field_id' in 'on clause'
This is the structure of that table
mysql> desc mantis_custom_field_project_table;
+------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------+------+-----+---------+-------+
| field_id | int(3) | NO | PRI | 0 | |
| project_id | int(7) unsigned | NO | PRI | 0 | |
| sequence | int(2) | NO | | 0 | |
+------------+-----------------+------+-----+---------+-------+
So as you can see there is a "field_id" column. I tried doing the join
without aliases (with the full table names) but the error is still there
ERROR 1054 (42S22): Unknown column
'mantis_custom_field_project_table.field_id' in 'on clause'
Have any ideas?
Thanks,
Florin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]