Query Help with version difference.

phibertek · Joined: 25 Feb 2008 Posts: 2

Hello, I have this query that worked on MySQL version 4.1.20, but when I moved to 5.0.22, the query bombs.

SELECT DISTINCT id,users.user_login as username,
meta_first.meta_value as firstname,
meta_last.meta_value as lastname,
users.user_pass as password,
users.user_nicename,
users.user_email as email,
users.user_url as url
FROM users,usermeta
INNER
JOIN usermeta as meta_first
ON meta_first.user_id = users.id
AND meta_first.meta_key = 'first_name'
INNER
JOIN usermeta as meta_last
ON meta_last.user_id = users.id
AND meta_last.meta_key = 'last_name'
WHERE users.user_login = 'markw'
AND usermeta.meta_key = 'last_name'

ERROR 1054 (42S22): Unknown column 'users.id' in 'on clause'

Can someone shed some light on this for me?

-Mark

phibertek · Joined: 25 Feb 2008 Posts: 2

***(users as users ,usermeta as usermeta)***
I guess they are making MySQL more compliant.

SELECT DISTINCT id,users.user_login as username,
meta_first.meta_value as firstname,
meta_last.meta_value as lastname,
users.user_pass as password,
users.user_nicename,
users.user_email as email,
users.user_url as url
FROM (users as users ,usermeta as usermeta)
INNER
JOIN usermeta as meta_first
ON meta_first.user_id = users.id
AND meta_first.meta_key = 'first_name'
INNER
JOIN usermeta as meta_last
ON meta_last.user_id = users.id
AND meta_last.meta_key = 'last_name'
WHERE users.user_login = 'markw'
AND usermeta.meta_key = 'last_name'