Subquery in SQLServer

[calgomez]

Hi, I have a query and a problem.

The problem is:

SELECT items.item as model,
COUNT(inspection_cells.ticket_id) AS qty,
(SELECT count(inspection_cells.ticket_id) FROM inspection_cells, movements, items WHERE inspection_cells.ticket_id = movements.ticket_id and movements.item_id = items.item_id and items.item = model and inspection_cells.air_time in (0,1) and (movements.movement_date between '01-01-2007' and '01-23-2007')) as qty_cero
FROM movements, items, inspection_cells
WHERE movements.item_id = items.item_id and
movements.ticket_id = inspection_cells.ticket_id and
(movements.movement_date between '01-01-2007' and '01-23-2007')
GROUP BY items.item
ORDER BY items.item

The "alias" model in first query it's the condition in the where in the second
query, but dont send me any error, but the result of qtycero it's not valid.

Any sugerence..???

[almaz]

In order to optimize query performance I would suggest you to rewrite the query as shown below:

SELECT  items.item as model, COUNT(inspection_cells.ticket_id) AS qty,
        sum(case when inspection_cells.air_time in (0, 1) then 1
                 else 0
            end) as qty_cero
FROM    movements inner join items
            on movements.item_id = items.item_id inner join inspection_cells
            on movements.ticket_id = inspection_cells.ticket_id
WHERE   movements.movement_date between '01-01-2007'
                                and     '01-23-2007'
GROUP BY items.item
ORDER BY items.item
 

Nevertheless, if you'll decide to stay with your current version: column aliases cannot be used to reference the column in any clauses except ORDER BY clause. So you'll have to add an alias for items table (like items as items_main) and reference the column by items_main.item.