Getting results to display that exist in table 1 but not table 2

Question

I'm new to SQL and am having trouble getting results to display that exist in table 1 but not table 2. I need to display how many times each ID from table 1 has been used in table 2 (including 0 if it has not been used) I can get the ID's that exist in Table 1 to display, but not the ID's that don't exist in Table 2.

I am getting:
       ID Count
          1
          1
          1
          1
          1
          1
          2

but need:
       ID Count 
          1
          1
          1
          0
          1
          1
          0
          1
          2

I have tried:

SELECT COUNT (PID) AS [ID Count]
FROM SalesOrderProduct
WHERE PID > = 0
GROUP BY PID;

(just for this column that i can't get the 0 values to display in)

Table 1: PID, Description
Table 2: PID, Status

How can I get the results to display showing all the counts for ID in Table 2, including when the count is 0 using UNION?

Thanks everyone

Answer 1

in this case that your ids are not unique use exists with a count plus a union like:

select distinct tbl.id, 0 cnt --for ids not exists in table2
from table1 tbl 
where not exists (select t.id from table2 t where t.id=tbl.id)
union
select t1.id, count(t1.id) cnt ----for ids exists in table2
from table1 t1
where exists (select t2.id from table2 t2 where t1.id=t2.id)
group by t1.id

Answer 2

Try this, you can change the attribute name based on your table structure.

Select t1.id, count(t2.id)

From t1 left join t2 
     on (t1.id = t2.id)

Group By t1.id;