First off this query works.... 99% the oddity is that I cant use one particular 'AS name' in my WHERE clause and its not allowing me to check against the proper data.... Basically when you get down to the IF statement that is AS user_type using "user_type" in any clause is ambiguous... so I've tried changing it to 'AS u_type' and that gets me an invalid or unknown column... yet MySQL docs say that you can use any alias in a clause.... Any ideas?
Just to describe the table, its a user table that connects 3 different tables together (phpBB user table, and another external database sars_id) but allows for accounts to be created UNDER a main account (master_userid) and thats where the convoluted JOINS come in to get all the data needed for that... An added level of convolution comes in when sub accts can NEVER inherit admin user_type....
Everything works like I said, except I cant use WHERE on my "massaged" user_type to get the proper values compared against
SELECT
u1.id,
u1.phpbb_id,
u1.sars_id,
u1.username,
u1.email,
u1.password,
u1.first_name,
u1.last_name,
CONCAT_WS(' ', u1.first_name, u1.last_name) AS full_name,
u1.company_name,
u1.company_address,
u1.company_city,
u1.company_state,
u1.company_postal,
u1.company_country,
u1.phone,
u1.fax,
IF(
IFNULL(
u1.master_userid,
-1
)>0,
MAKE_SET(u2.user_type+0, 'support_user', 'prc_user'),
u1.user_type
) AS user_type,
u1.master_userid,
u2.prc_tier AS prc_tier,
u2.prc_type AS prc_type,
u2.prc_geography AS prc_geography,
UNIX_TIMESTAMP(u1.modified) AS modified,
u3.username as modified_by,
UNIX_TIMESTAMP(u1.created) AS created,
u4.username as created_by
FROM
`common_users` AS u1
JOIN
`common_users` AS u2
ON
u2.id=IFNULL(u1.master_userid,u1.id)
JOIN
`common_users` AS u3
ON
u3.id=u1.modified_by
JOIN
`common_users` AS u4
ON
u4.id=u1.created_by
WHERE
user_type & 1