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

[Tompkins Neil]

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