From: Afivi Andri Sagala (afivi_asbinasanprima.com)
Date: Thu Nov 10 2005 - 02:17:19 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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" <florin.ochianardsnet.ro>
To: <mysqllists.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/mysql?unsub=afivi_asbinasanprima.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]