SQL & PL/SQL

Hi
Thanks so much for everyone who has responded to my questions thus far. I'd be lost without the support.
Anywho,
I have two tables, TARGETS

TARGET_ID   LOCATION_ID
    1             10
    2             11
    3             11
    4             11
    5             12
    ..             ..

and MISSIONS_TARGETS

MISSON_ID   TARGET_ID
    1              1
    1              2
    1              3
    1              4 
    2              5
   ..              ..

I have written a query to count the number of targets who share the same location as the target_id: (I hope this is right!!)

SELECT
      t.target_id,
      count(t.target_id)-1
FROM
      targets t
INNER JOIN
      targets t1
ON
      t.location_id=t1.location_id
GROUP BY
      t.target_id

What I am trying and failing to do is add a third column to this query to count the number of targets who have been on the same mission as the target_id. i.e if target_id=24 should return count of 2 (i.e.11,19) in the third column. Whenever I add another count expression and join the MISSIONS_TARGETS table to the query above, the first count of the targets double and the count is waay off. I think I should be using count(distinct..)? here but every time I do that the results make no sense either. If anyone could point me in the right direction of how to add a count expression and join the MISSION_TARGETS table to get the results I need, I woud really appreciate it. Thanks

Edited by: user8695469 on 20-Aug-2009 09:38

Edited by: user8695469 on 20-Aug-2009 10:02