Some discussion of causes for this is at: http://dev.mysql.com/doc/refman/5.0/en/join.html
Look for the part beginning: "Join Processing Changes in MySQL 5.0.12" On Nov 16, 2010, at 10:09 AM, Tompkins Neil wrote: > Hi > > I've the following query : > > SELECT players_bids.players_bids_id, players_bids.players_id, > players_bids.bid_date, players_bids.bid_type, players_bids.bid_value, > (SELECT SUM(IF(home_users_id = > players_bids.users_id_from,home_manager_points,away_manager_points)) FROM > fixtures_results WHERE (home_users_id = players_bids.users_id_from OR > away_users_id = players_bids.users_id_from) AND worlds_id = 1) AS > manager_points, > players_bids.users_id_from, > (SELECT ROUND((SUM(won_home)+SUM(won_away))/COUNT(*)*100,0) > FROM > (SELECT IF(home_goals > away_goals, 1, 0) AS won_home ,0 AS won_away > FROM fixtures_results WHERE (home_users_id = players_bids.users_id_from) AND > status = 'approved' > UNION ALL > SELECT > 0 AS won_home > ,IF(away_goals > home_goals, 1, 0) as won_away > FROM fixtures_results WHERE away_users_id = players_bids.users_id_from AND > status = 'approved') s1) AS wins, > players_bids.users_id_to, players_bids.worlds_id, players_bids.seasons_id, > players_master.first_name, players_master.second_name, > players_master.known_as, players_master.estimated_value, > players_master.rating, > players_master.positions_id, players.games_played, > players_bids.teams_id_from, players_bids.teams_id_to, > teams_master_from.team_name AS team_name_from, teams_master_to.team_name AS > team_name_to > FROM players_bids > INNER JOIN players ON players_bids.players_id = players.players_id > INNER JOIN players_master ON players_bids.players_id = > players_master.players_id > INNER JOIN teams_master teams_master_from ON players_bids.teams_id_from = > teams_master_from.teams_id > INNER JOIN teams_master teams_master_to ON players_bids.teams_id_to = > teams_master_to.teams_id > WHERE players_bids.bid_status = 'accepted' > AND players_bids.players_id = 279 > AND players_bids.worlds_id = 1 > ORDER BY players_bids.bid_type ASC, players_bids.bid_value DESC, > manager_points DESC, players_bids.bid_date ASC > > but the problem I have is that when referencing players_bids.users_id_from > within the UNION I get back the following error : > > Error Code: 1054 > Unknown column 'players_bids.users_id_from' in 'where clause' > > Any ideas how to overcome this problem ? > > Cheers > Neil -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
