CODEWITHSUNDEEP
sqlsql-server-2008
RobHurd
RobHurd

Reputation: 2061

SQL: How to include SubQuery column result in WHERE clause?

How do I include the result of a sub query into a WHERE clause?

For example, I have the following statement (cut down because the original is large):

    SELECT   o.ID AS OrderSpecsID, o.CustomerID, o.EstimateNo, o.OrderYear,

        (SELECT  COUNT(*) 
                 FROM  OrderSpecs AS os 
                 WHERE (o.OrderID = OrderID)) AS AmendmentCount

         FROM  OrderSpecs AS o LEFT OUTER JOIN 
               Orders ON o.OrderID = Orders.ID

   WHERE (o.CustomerID = 30)

I want to include the AmendmentCount field in my WHERE clause like so:

WHERE (o.CustomerID = 30) AND (AmendmentCount > 0)

However, if I set the above I get the following error:

Invalid column name 'AmendmentCount'

How can I make the AmendmentCount field available to my WHERE clause?

Many thanks,

Rob

Upvotes: 0

Views: 169

Answers (2)

Paulo Lima
Paulo Lima

Reputation: 1238

you can put your select with a join in a query

FROM  OrderSpecs
join (SELECT  COUNT(*), OrderID  
             FROM  OrderSpecs AS os 
             WHERE (o.OrderID = OrderID)) AS Amendment on Amendment.OrderID = OrderSpecs.OrderID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use a CTE or subquery:

with cte as (
      SELECT o.ID AS OrderSpecsID, o.CustomerID, o.EstimateNo, o.OrderYear,
             (SELECT  COUNT(*) 
                      FROM  OrderSpecs AS os 
                      WHERE (o.OrderID = OrderID)
             ) AS AmendmentCount
      FROM  OrderSpecs AS o LEFT OUTER JOIN 
            Orders
            ON o.OrderID = Orders.ID
    )
select *
from cte
where (CustomerID = 30) and (AmendmentCount > 0);

If your case, though, the better way to write the query is probably to use window functions:

select os.*
from (select os.ID AS OrderSpecsID, os.CustomerID, os.EstimateNo, os.OrderYear,
             count(o.OrderId) over (partition by os.OrderId) as AmendmentCount
      from OrderSpecs os left outer join
           Orders o
           on os.OrderID = o.ID
     ) os
where (CustomerID = 30) and (AmendmentCount > 0);

I am a little unclear if the filter on CustomerId should be in the subquery or outer query in this case. One or the other should work for what you are doing.

Upvotes: 3

Related Questions