Unknown column "users.users_id" in "where clause"

Unknown column "users.users_id" in "where clause"

am 03.02.2011 14:55:36 von Tompkins Neil

--0015175ca7beb63a04049b611e6e
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I've the following SELECT statement

SELECT users.gamer_tag, UNIX_TIMESTAMP(users.created_on) AS time_registered,
(SELECT fixtures_results.last_changed
FROM fixtures_results
WHERE (home_users_id = users.users_id AND home_teams_id =
users_teams.teams_id)
OR (away_users_id = users.users_id AND away_teams_id = users_teams.teams_id)
AND fixtures_results.status = 'approved'
ORDER BY fixtures_results.last_changed ASC LIMIT 1) AS time_at_team,
(SELECT COUNT(DISTINCT(teams_id))
FROM
(SELECT DISTINCT(home_teams_id) AS teams_id
FROM fixtures_results
WHERE (fixtures_results.home_users_id = *users.users_id*)
AND fixtures_results.status = 'approved'
UNION ALL
SELECT DISTINCT(away_teams_id) AS teams_id
FROM fixtures_results
WHERE (fixtures_results.away_users_id = *users.users_id*)
AND fixtures_results.status = 'approved') s1) AS number_teams
FROM users
INNER JOIN users_teams ON users.users_id = users_teams.users_id
WHERE UNIX_TIMESTAMP(users.created_on) > 0
AND (SELECT fixtures_results.last_changed
FROM fixtures_results
WHERE (home_users_id = users.users_id AND home_teams_id =
users_teams.teams_id)
OR (away_users_id = users.users_id AND away_teams_id = users_teams.teams_id)
AND fixtures_results.status = 'approved'
ORDER BY fixtures_results.last_changed ASC LIMIT 1) <> ''
ORDER BY time_at_team ASC, time_registered ASC

Any ideas why I'm getting Unknown column 'users.users_id' in 'where clause'
for the part of the statement that I've marked as *bold* and how I can over
come this problem ?

Cheers
Neil

--0015175ca7beb63a04049b611e6e--

Re: Unknown column "users.users_id" in "where clause"

am 03.02.2011 17:22:49 von Simcha

On Thu, 3 Feb 2011 13:55:36 +0000
Tompkins Neil wrote:

> SELECT DISTINCT(away_teams_id) AS teams_id
> FROM fixtures_results
> WHERE (fixtures_results.away_users_id = *users.users_id*)

> Any ideas why I'm getting Unknown column 'users.users_id' in 'where clause'
> for the part of the statement that I've marked as *bold* and how I can over
> come this problem ?

You have to add the users table to the subquery.
Your subquery only has the fixtures_results table in the 'from' clause, and the subquery is not aware of the tables you use in the outer query.

--
Simcha Younger

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

Re: Unknown column "users.users_id" in "where clause"

am 04.02.2011 11:22:11 von Tompkins Neil

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

Thanks, but how can I pass the current users value from the other query ?

On Thu, Feb 3, 2011 at 4:22 PM, Simcha Younger wrote:

> On Thu, 3 Feb 2011 13:55:36 +0000
> Tompkins Neil wrote:
>
> > SELECT DISTINCT(away_teams_id) AS teams_id
> > FROM fixtures_results
> > WHERE (fixtures_results.away_users_id = *users.users_id*)
>
> > Any ideas why I'm getting Unknown column 'users.users_id' in 'where
> clause'
> > for the part of the statement that I've marked as *bold* and how I can
> over
> > come this problem ?
>
> You have to add the users table to the subquery.
> Your subquery only has the fixtures_results table in the 'from' clause, and
> the subquery is not aware of the tables you use in the outer query.
>
> --
> Simcha Younger
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=neil.tompkins@googlemail. com
>
>

--00163630fc0556cf59049b724149--