I need help with a subquery!

[iRead]

I have a SELECT statement with a subquery. I use an alias as I add the results of the subquery to the dataset. I then try to use the alias in the WHERE clause of the SELECT statement. I get an “Invalid column name “ message with this code:

select i.id as itemid,
(select top 1 ca2.itemid from itemassign ca2
inner join account a2 on ca2.accountid=a2.id
where a2.customerid=c.id and ca2.itemid=i.id)
as iaid
from item i
inner join customer c on i.customerid=c.id
where i.customerid=1 and iaid is null
order by i.id DESC


Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'iaid'.

If I run the statement without the and condition in the WHERE clause it returns a valid result.
Any input on this will be very appreciated!

Thank you,
IRead

 

[apexbs]

Use Derived Table in FROM clause like that.

select i.id as itemid , iaid.TopItemID
from item i
inner join customer c on i.customerid=c.id left join
(select top 1 ca2.itemid as TopItemID from itemassign ca2
inner join account a2 on ca2.accountid=a2.id
where a2.customerid=c.id and ca2.itemid=i.id)
as iaid On i.id = iaid.ItemID
where i.customerid=1 and iaid.TopItemID is null
order by i.id DESC

Try this and get back to me.