Error Unknown column in "where clause"

--00163630f84933342a04952dc997
Content-Type: text/plain; charset=ISO-8859-1

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

--00163630f84933342a04952dc997--

Re: Error Unknown column in "where clause"

I have run into similar issues in far less convoluted circumstances.
I'm not going to try to decode your query but using an alias can help
clarify things.
...
team_name_to
FROM players_bids AS PB
INNER JOIN players ON players_bids.players_id =3D players.players_id
....

and rename all your player_bids to reference to the alias.

It usually resolves the problem for me.

=A0- michael dykman

> On Tue, Nov 16, 2010 at 11:09 AM, Tompkins Neil
> wrote:
>> Hi
>>
>> I've the following query :
>>


>>
>> but the problem I have is that when referencing players_bids.users_id_fr=
om
>> 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
>>
>
>
>
> --
> =A0- michael dykman
> =A0- mdykman@gmail.com
>
> =A0May the Source be with you.
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Error Unknown column in "where clause"

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
>=20
> I've the following query :
>=20
> 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 =3D
> players_bids.users_id_from,home_manager_points,away_manager_ points)) =
FROM
> fixtures_results WHERE (home_users_id =3D players_bids.users_id_from =
OR
> away_users_id =3D players_bids.users_id_from) AND worlds_id =3D 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 =3D =
players_bids.users_id_from) AND
> status =3D '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 =3D =
players_bids.users_id_from AND
> status =3D '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 =3D players.players_id
> INNER JOIN players_master ON players_bids.players_id =3D
> players_master.players_id
> INNER JOIN teams_master teams_master_from ON =
players_bids.teams_id_from =3D
> teams_master_from.teams_id
> INNER JOIN teams_master teams_master_to ON players_bids.teams_id_to =3D
> teams_master_to.teams_id
> WHERE players_bids.bid_status =3D 'accepted'
> AND players_bids.players_id =3D 279
> AND players_bids.worlds_id =3D 1
> ORDER BY players_bids.bid_type ASC, players_bids.bid_value DESC,
> manager_points DESC, players_bids.bid_date ASC
>=20
> but the problem I have is that when referencing =
players_bids.users_id_from
> within the UNION I get back the following error :
>=20
> Error Code: 1054
> Unknown column 'players_bids.users_id_from' in 'where clause'
>=20
> Any ideas how to overcome this problem ?
>=20
> Cheers
> Neil

--=20
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=3Dgcdmg-mysql-2@m.gmane.o rg