MS SQL SubQuery for count

[crmayer]

I was wondering if somebody could help me, or tell me if this is even possible.
I have a query that returns what I need, but now I have been asked to add a count to it that comes from another query.
This is what I am working with so far.
(My addition to the previously working query is the (select count(...)part of this.

select
tractor.id tractor_id,
tractor.dispatcher driver_manager,
tractor.driver1_id driver_id,
(select count(*)
from movement, equipment_item
where movement.equipment_group_id = equipment_item.equipment_group_id
and movement.status = 'A'
and equipment_item.equipment_type_id = 'T'
and equipment_item.equipment_id = tractor.id) as preassignments,
movement_order.order_id order_id,
DATEDIFF(day, stop.actual_arrival, GETDATE()) days,
stop.actual_arrival actual_arrival,
stop.city_name city_name,
stop.state stop_state,
location.name loc_name

from
tractor,
equipment_group left outer join equipment_item equipment_item on equipment_item.equipment_group_id = equipment_group.id
and equipment_item.equipment_type_id = 'T' and equipment_item.type_sequence = 0 and equipment_item.company_id = 'TMS',
movement left outer join movement_order on movement_order.movement_id = movement.id and movement_order.company_id = 'TMS',
stop left outer join location on location.id = stop.location_id and location.company_id = 'TMS'

where tractor.company_id = 'TMS'
and tractor.service_status = 'A'
and tractor.fleet_id <> '99'
and tractor.currenteqpgrpid = equipment_group.id
and equipment_group.company_id = 'TMS'
and movement.equipment_group_id = equipment_group.id
and equipment_group.currentmovement_id=movement.id
and movement.company_id = 'TMS'
and stop.id = movement.dest_stop_id
and stop.actual_arrival < DATEADD(day, -5, GETDATE())
and stop.company_id = 'TMS'


order by days desc

This does run, but I get an error: Could not parse sql statement to get count.

Am I even close to getting what I want?
What I would like to see is:
tractor_id | driver_man | driver_id | preassignments | order_id | days | actual_arrival | city_name | stop_state | loc_name.

If this is not possible, I could just exclude rows that have a count > ZERO. That is my ultimate goal, but I can filter those out in my report if needed.

Thanks in advance.....
- Chris

 

[hmckillop]

See one thing for starter, you could alias you tables and make it alot shorter.

Try this, I think you had the movement table twice.
Not sure though

select 
  tr.id tractor_id,
  tr.dispatcher driver_manager,
  tr.driver1_id driver_id,
  (select count(*)
      from  equipment_item eq1
      where mov.equipment_group_id = eq1.equipment_group_id
      and mov.status = 'A'
      and eq1.equipment_type_id = 'T'
      and eq1.equipment_id = tr.id) as preassignments,
  moo.order_id order_id, 
  DATEDIFF(day, st.actual_arrival, GETDATE()) days,
  st.actual_arrival actual_arrival, 
  st.city_name city_name, 
  st.state stop_state, 
  location.name loc_name 
     
from 
  tractor tr,
  equipment_group eqg left outer join equipment_item eqi 
		on eqi.equipment_group_id = eqg.id and  eqi.equipment_type_id = 'T' and eqi.type_sequence = 0 and  eqi.company_id = 'TMS',
  movement mov left outer join movement_order on moo.movement_id = mov.id and moo.company_id = 'TMS',
  stop st left outer join location on location.id = st.location_id and location.company_id = 'TMS'  

where tr.company_id = 'TMS' 
  and tr.service_status = 'A' 
  and tr.fleet_id <> '99'
  and tr.currenteqpgrpid = eqg.id
  and eqg.company_id = 'TMS' 
  and mov.equipment_group_id = eqg.id      
  and eqg.currentmovement_id=mov.id 
  and mov.company_id = 'TMS' 
  and st.id = mov.dest_stop_id 
  and st.actual_arrival < DATEADD(day, -5, GETDATE())
  and st.company_id = 'TMS'

order by days desc

"I'm living so far beyond my income that we may almost be said to be living apart

 

[crmayer]

for some reason I do not think it likes something in what I would call the sub query (very possible I am using the wrong terminology)....

I get that same error.

But you are right, I did have the movement table in there twice.

 

[crmayer]

My apologizes, I got this working.
Come to find out that if I write this query in our 3rd party SQL application I get that error. If I put this query directly in the query analyzer it runs fine.

Thanks for the help on this, I guess I learned a valuable lesson today.

 

[markros]

Use derived tables instead of projection. They perform much better in general.