SQL Join and Count

Question

I want to join two tables and get data against an ID from the first table and count a column record from the second table against the same ID. I want a single query which gives me that output.

Answer 1

I think you're asking about a query like this:

select t1.id, count(t2.id)
 from table1 as t1
 left outer join table2 as t2
  on t2.table1_id = t1.id
 group by t1.id;

Answer 2

select 
   ID,
   (select count(*) from table2 where ID=p.ID) as [count]
from table1 p

Answer 3

Following is a use-case/example for your problem and a proposed solution: You have two tables User and User_Friends which store user-data and contact-information respectively.

And you want to display the name and number of contacts a user has.

Table User:
id Name
0   A
1   B
2   C
3   D

Table User_Friends:
id friend_id
0    1
0    2
0    3
1    2
1    3

Output:
Name    Count(*)
A        3
B        2
C        0
D        0

//Display the Name, number of friends
SELECT Name, count(*)
FROM User, User_Friends
WHERE User.id = User_Friends.id
GROUP BY User_Friends.id