Query Question

Databases
1

Why can’t I access prostatus in WHERE clause, whilst I can access it ORDER BY clause.

Eg:
SELECT …,IF((p2.pr_status = 0 OR psd2.link_status = 0),0,1) AS prostatus
FROM …
WHERE
prostatus = 1 AND p2.pr_type_id = 1 …
<- Doesn’t work (Unknown column ‘prostatus’ in ‘where clause’). Yet, the following DOES work:

SELECT …,IF((p2.pr_status = 0 OR psd2.link_status = 0),0,1) AS prostatus
FROM …
WHERE
p2.pr_type_id = 1 …
ORDER BY prostatus DESC

Assuming everything else is the same in the query.

Thanks

2

No. you can’t use that alias in the where clause. You may try to query like:

WHERE
(IF((p2.pr_status = 0 OR psd2.link_status = 0),0,1) = 1) AND p2.pr_type_id = 1

3

Thanks. I worked out I could also do HAVING prostatus = 1 after the WHERE clause.
Do you see any complications with doing it this way?