Subquery in SQLServer

[calgomez]

Hi, I have a query and a problem.

The problem is:

SELECT items.item as model,
COUNT(inspectio n_cells.ticket_ id) AS qty,
(SELECT count(inspectio n_cells.ticket_ id) FROM inspection_cell s, movements, items WHERE inspection_cell s.ticket_id = movements.ticke t_id and movements.item_ id = items.item_id and items.item = model and inspection_cell s.air_time in (0,1) and (movements.move ment_date between '01-01-2007' and '01-23-2007')) as qty_cero
FROM movements, items, inspection_cell s
WHERE movements.item_ id = items.item_id and
movements.ticke t_id = inspection_cell s.ticket_id and
(movements.move ment_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.