MySQL query within if condition

Question

I have the following tables:

USERS:

id,username,owner

ADMINS

id,username,owner

TRANSACTIONS:

id,sender_id,sender_type

Each user can be owned by an admin Each admin can be owned by another admin

I am trying to work on some access levels and I want to retrieve the query rows that have the owner of the sender part of a list generated through PHP.

For this I need to get the owner of the sender based on the data I have, without having to alter the database structure:

SELECT 
    * ,
    IF (t.sender_type='admin',
        ( SELECT owner AS qowner 
          FROM admins
          WHERE id=t.sender_id),
          ( SELECT owner AS qowner FROM users 
            WHERE id=t.sender_id)
    ) AS qowner 
FROM `transactions` t 
WHERE qowner IN ('admin22','admin33','admin44','admin66')

I keep getting this error : Unknown column 'qowner' in 'where clause'

I am now stuck at this. I will appreciate all help coming. Thank you!

You are using `qowner` alias three times for different things, that's why the error — Hanky Panky, Oct 04 '13 at 15:03
I removed the aliases and used it only after the IF query and still same issue. — NVG, Oct 04 '13 at 15:12
That is because you are trying to use an alias in the where clause. The alias name effectively doesn't exist until after the WHERE clause has been evaluated - http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql — Kickstart, Oct 04 '13 at 15:24

score 3 · Accepted Answer · answered Oct 04 '13 at 15:11

3

You appear to be trying to refer to a column from the subselects within the WHERE clause I think.

However no need for subselects:-

SELECT * ,
IF (t.sender_type='admin', a.owner, b.owner) AS qowner 
FROM transactions t 
LEFT OUTER JOIN admins a ON t.sender_id = a.id
LEFT OUTER JOIN users b ON t.sender_id = a.id
HAVING qowner IN ('admin22','admin33','admin44','admin66')

answered Oct 04 '13 at 15:11

Kickstart

21,403
2
21
33

Yes, I am trying to refer to a column from the subselects. Will try this and let you know – NVG Oct 04 '13 at 15:15
I used my own query, but instead of WHERE, I used HAVING, and it worked! – NVG Oct 04 '13 at 15:35

score 0 · Answer 2 · answered Oct 04 '13 at 15:05

0

SELECT * ,
IF (t.sender_type='admin',(SELECT owner FROM admins WHERE id=t.sender_id),(SELECT owner FROM users WHERE id=t.sender_id)) AS qowner 
FROM transactions t 
WHERE qowner IN ('admin22','admin33','admin44','admin66')

(untested)

answered Oct 04 '13 at 15:05

Moob

14,420
1
34
47

Modified the query into this: "SELECT * ,IF (t.sender_type='admin',(SELECT owner FROM admins WHERE id=t.sender_id),(SELECT owner FROM users WHERE id=t.sender_id)) AS qowner FROM `transactions` t WHERE qowner IN ('admin22','admin33','admin44','admin66')" and I am getting same error. Thanks for the help anyway – NVG Oct 04 '13 at 15:12

MySQL query within if condition

2 Answers2