CODEWITHSUNDEEP
mysqlsqlif-statementcaseconditional-statements
NVG
NVG

Reputation: 3313

MySQL query within if condition

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!

Upvotes: 0

Views: 10759

Answers (2)

Kickstart
Kickstart

Reputation: 21513

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')

Upvotes: 3

Moob
Moob

Reputation: 16184

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)

Upvotes: 0

Related Questions