axor axor - 4 months ago 6
SQL Question

How do I reference 2 aliases and compare them in WHERE clause?

I have this

RECORD ITEMS ITEMSTOTAL
------------------------------------ ---------- ----------
ababababaa 0 1
ababababab 0 0
ababababac 0 1
ababababad 1 1
ababababae 0 2


but I need this output when ITEMS=ITEMSTOTAL

RECORD
------------------------------------
ababababab
ababababad


Currently I'm using this query for the first result, but I don't know how to get the second output, Maybe this sounds obvious but I can't find the answer :(

SELECT RECORD,
(SELECT COUNT(*) FROM TABLE1 WHERE SOMETHING=X) AS ITEMS,
(SELECT COUNT(*) FROM TABLE2 WHERE SOMETHING2=Y) AS ITEMSTOTAL
FROM RECORDS_TABLE
WHERE DELETED=0
--and ITEMS.count = ITEMSTOTAL.count <-- tried something like this but it doesn't work.

Answer

One option would be to use a subquery and apply the where criteria to the outer query.

select * 
from (
   your query here
) t
where items = itemstotal

I assume that isn't your actual query btw. where comes after from. Also, those count statements would return the same values.

Comments