Professional Web Applications Themes

Problem with a query - MySQL

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 ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Problem with a query


    Christoph wrote: 

    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 Guest

  3. #3

    Default Re: Problem with a query

    strawberry wrote: 
    >> +------------+ 
    >> +------------+
    >> 1 row in set (0.00 sec)
    >>
    >> thnx,
    >> Christoph[/ref]
    >
    > 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;[/ref]

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


    Paul Guest

Similar Threads

  1. query problem
    By Tony Rogerson in forum Coldfusion Database Access
    Replies: 35
    Last Post: November 2nd, 03:01 PM
  2. ***Sql Query problem
    By Thomas 'PointedEars' Lahn in forum MySQL
    Replies: 0
    Last Post: December 26th, 11:21 PM
  3. Query of Query problem
    By JakeFlynn in forum Macromedia ColdFusion
    Replies: 18
    Last Post: August 11th, 08:15 AM
  4. Query on Query and CF casting problem
    By obxlefty in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: May 18th, 12:44 PM
  5. Query Problem
    By Stuart in forum ASP
    Replies: 4
    Last Post: October 30th, 01:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139