Problem with a query

I have the following query:

SELECT
TRIM( trade_game_pieces.id ) AS id,
TRIM( from_user.name ) AS from_user_name,
TRIM( to_user.name ) AS to_user_name,
TRIM( games.game_name ) AS game_name,
TRIM( sets.set_name ) AS set_name,
TRIM( game_pieces.piece_name ) AS piece_name,
TRIM( trade_game_pieces.trade_direction ) AS trade_direction,
TRIM( trade_game_pieces.number_to_trade ) AS number_to_trade
FROM
trade_game_pieces
INNER JOIN game_pieces ON game_pieces.id = trade_game_pieces.game_piece_id
INNER JOIN games_sets ON games_sets.id = game_pieces.games_set_id
INNER JOIN sets ON sets.id = games_sets.set_id
INNER JOIN games ON games.id = games_sets.game_id
INNER JOIN trades ON trades.id = trade_game_pieces.trade_id
INNER JOIN users from_user ON from_user.id = trades.from_user_id
INNER JOIN users to_user ON to_user.id = trades.to_user_id;

and whenever I try to run it I get the following error:

ERROR 1054 (42S22): Unknown column 'from_user.name' in 'field list'

Why? Is it because I'm using an alias table name in a function? If so, is
there a work around?

mysql> select version();
+------------+
| version() |
+------------+
| 5.0.18-log |
+------------+
1 row in set (0.00 sec)

thnx,
Christoph

Christoph wrote:
> I have the following query:
>
> SELECT
> TRIM( trade_game_pieces.id ) AS id,
> TRIM( from_user.name ) AS from_user_name,
> TRIM( to_user.name ) AS to_user_name,
> TRIM( games.game_name ) AS game_name,
> TRIM( sets.set_name ) AS set_name,
> TRIM( game_pieces.piece_name ) AS piece_name,
> TRIM( trade_game_pieces.trade_direction ) AS trade_direction,
> TRIM( trade_game_pieces.number_to_trade ) AS number_to_trade
> FROM
> trade_game_pieces
> INNER JOIN game_pieces ON game_pieces.id = trade_game_pieces.game_piece_id
> INNER JOIN games_sets ON games_sets.id = game_pieces.games_set_id
> INNER JOIN sets ON sets.id = games_sets.set_id
> INNER JOIN games ON games.id = games_sets.game_id
> INNER JOIN trades ON trades.id = trade_game_pieces.trade_id
> INNER JOIN users from_user ON from_user.id = trades.from_user_id
> INNER JOIN users to_user ON to_user.id = trades.to_user_id;
>
> and whenever I try to run it I get the following error:
>
> ERROR 1054 (42S22): Unknown column 'from_user.name' in 'field list'
>
> Why? Is it because I'm using an alias table name in a function? If so, is
> there a work around?
>
> mysql> select version();
> +------------+
> | version() |
> +------------+
> | 5.0.18-log |
> +------------+
> 1 row in set (0.00 sec)
>
> thnx,
> Christoph

this doesn't look right:

INNER JOIN users from_user ON from_user.id = trades.from_user_id
INNER JOIN users to_user ON to_user.id = trades.to_user_id;

perhaps it should be:

INNER JOIN from_user ON from_user.id = trades.from_user_id
INNER JOIN to_user ON to_user.id = trades.to_user_id;

strawberry wrote:
> Christoph wrote:
>> I have the following query:
>>
>> SELECT
>> TRIM( trade_game_pieces.id ) AS id,
>> TRIM( from_user.name ) AS from_user_name,
>> TRIM( to_user.name ) AS to_user_name,
>> TRIM( games.game_name ) AS game_name,
>> TRIM( sets.set_name ) AS set_name,
>> TRIM( game_pieces.piece_name ) AS piece_name,
>> TRIM( trade_game_pieces.trade_direction ) AS trade_direction,
>> TRIM( trade_game_pieces.number_to_trade ) AS number_to_trade
>> FROM
>> trade_game_pieces
>> INNER JOIN game_pieces ON game_pieces.id =
>> trade_game_pieces.game_piece_id INNER JOIN games_sets ON
>> games_sets.id = game_pieces.games_set_id
>> INNER JOIN sets ON sets.id = games_sets.set_id
>> INNER JOIN games ON games.id = games_sets.game_id
>> INNER JOIN trades ON trades.id = trade_game_pieces.trade_id
>> INNER JOIN users from_user ON from_user.id = trades.from_user_id
>> INNER JOIN users to_user ON to_user.id = trades.to_user_id;
>>
>> and whenever I try to run it I get the following error:
>>
>> ERROR 1054 (42S22): Unknown column 'from_user.name' in 'field list'
>>
>> Why? Is it because I'm using an alias table name in a function? If
>> so, is there a work around?
>>
>> mysql> select version();
>> +------------+
>>> version() |
>> +------------+
>>> 5.0.18-log |
>> +------------+
>> 1 row in set (0.00 sec)
>>
>> thnx,
>> Christoph
>
> this doesn't look right:
>
> INNER JOIN users from_user ON from_user.id = trades.from_user_id
> INNER JOIN users to_user ON to_user.id = trades.to_user_id;
>
> perhaps it should be:
>
> INNER JOIN from_user ON from_user.id = trades.from_user_id
> INNER JOIN to_user ON to_user.id = trades.to_user_id;

It could be correct he could be doing 2 joins to the users tabe under
separate aliases.