MySQL: SELECT EXISTS() AS field WHERE field = x

Question

The problem: I have an inventory table, and a table listing items that are being auctioned off. I want to have an alias field ("isAuction") to represent whether or not an item with inventory's stock number is present in the auction items table.

I wrote the following Query:

SELECT FROM inventory AS i
EXISTS(SELECT * FROM auctionitems AS a WHERE a.stockNo = i.stockNo) AS "isAuction" 

This does populate "isAuction" field with 1 or 0, as required.

I now add a WHERE condition:

SELECT FROM inventory AS i
EXISTS(SELECT * FROM auctionitems AS a WHERE a.stockNo = i.stockNo) AS "isAuction" 
WHERE isAuction = 1

However, when I add a WHERE condition, I get an error: "#1054 - Unknown column 'isAuction' in 'where clause'"

Two questions: 1) Where did I get it wrong? 2) Is there a better way to solve my problem?

Answer 1

Try HAVING isAuction = 1.

I read somewhere that HAVING is more awake of calculated columns than WHERE.

Answer 2

SELECT i.*
FROM inventory AS i
WHERE EXISTS(SELECT * FROM auctionitems AS a WHERE a.stockNo = i.stockNo)