List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 17 2010 5:59pm
Subject:Re: Error Unknown column in 'where clause'
View as plain text  
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

Thread
Error Unknown column in 'where clause'Tompkins Neil16 Nov
  • Re: Error Unknown column in 'where clause'Paul DuBois17 Nov
Re: Error Unknown column in 'where clause'Michael Dykman16 Nov