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

    Hmm... PostgreSQL doesn't like computed aliases in WHERE clauses either. I'm guessing it's because the WHERE clause is used to filter results before the computed return columns are evaluated.

    You might have to put the IF statement in the WHERE clause as well.

      Weedpacket;10907316 wrote:

      Hmm... PostgreSQL doesn't like computed aliases in WHERE clauses either. I'm guessing it's because the WHERE clause is used to filter results before the computed return columns are evaluated.

      You might have to put the IF statement in the WHERE clause as well.

      hmmm but even a vanilla query with a column name as the WHERE clause is failing though 🙁

      (and it shows this working in the MySQL examples)
      I'll try moving the IF to the WHERE as well and see what happens though.

      Thanks for the idea Weed 🙂

        Weedpacket;10907316 wrote:

        Hmm... PostgreSQL doesn't like computed aliases in WHERE clauses either. I'm guessing it's because the WHERE clause is used to filter results before the computed return columns are evaluated.

        You might have to put the IF statement in the WHERE clause as well.

        Brilliant Weed, using the IF in the WHERE while being sub optimal (this query probably is a beast anyways when it comes to optimzation) it works.... and its only queried very limitedly anyways, so it should be fine 🙂

        Thanks again!
        Glad to see you are still around after all these years, been awhile since I've been around 🙂

          tekky wrote:

          Glad to see you are still around after all these years, been awhile since I've been around

          I've got nowhere else to go!

          But you - you've gone back to College, I see. How was it while you were away?

            Weedpacket;10907357 wrote:

            I've got nowhere else to go!

            But you - you've gone back to College, I see. How was it while you were away?

            Heh, I wish, just happens to be the name of where I live 😛 I got out of the USAF and came back home after 7 years 😛

              From one wingnut to another, welcome back to the civilian life. I got out years ago.

              Like Weedpacket said, you can't reference select list items in a where clause cause they don't exist yet. However, you can put the same thing in the where clause that you used to create the alias in the select list. Most databases have enough brain power not to fire off the same thing twice and reuse what is in the where clause. The other option is to use a subselect. On pgsql this is quite efficient. In some older versions of MySQL (i.e. anything before 5.1) subselects are often turned into a nested join which sucks majorly for large lists. So, the subselect may look prettier but run more slowly than just putting the maths into the where clause.

              select a+b as ab, c from table where a+b < 2;

              i.e. that kinda thing.

                Sxooter;10907394 wrote:

                From one wingnut to another, welcome back to the civilian life. I got out years ago.

                Heh Thanks, I was actually stationed in Denver for 4 years and CoSp for 2 😛

                As for the query, I now have it working 🙂

                  Write a Reply...