SQL Select all from table A with counting table B

If I needed a query such that I grab all columns from table A but I also need to count how many B's each row in table A has.

Table A: id | username | email | address

Table B: user_id

SELECT *, total 
  FROM table_a 
 WHERE total = (SELECT * FROM table_b WHERE table_a.id==table_b.user_id)

Any ideas?

Edit: For more clarification here is t开发者_运维知识库he desired output

1 | steve | steve@steve.steve | 123 Steve | 5 // letters
2 | chris | chris@chris.chris | 123 chris | 2 // letters

---

SELECT 
     table_a.id, 
     table_a.username, 
     table_a.email, 
     table_a.address, 
     count(table_b.user_id) as total
 FROM table_a 
          LEFT OUTER JOIN table_b 
             ON table_a.id = table_b.user_id
  GROUP BY (
              table_a.id, 
              table_a.username, 
              table_a.email, 
              table_a.address
           )

This is a good example of needing an outer join. If we used an inner join, the query would exclude the entries in table_a which has zero table_b entries.

This could be further refined to meet two challenges:

• include all of the columns of table_a without explicitly asking for them.
• Handle the zero entry scenario without using non-standard SQL (eg ISNULL, WHERE)

This code below should do it.

SELECT
     table_a.*, 
     tempTable.total
FROM (
       SELECT
           table_a.Id,
           COUNT(table_b.user_id) as total
       FROM table_a
          LEFT OUTER JOIN table_b 
             ON table_a.id = table_b.user_id
        GROUP BY (table_a.id)
     ) AS tempTable
INNER JOIN table_a 
     ON tempTable.Id = table_a.Id;

Comparing this with Cybernate's solution, non-standard SQL looks very attractive :-)

---

Try this:

SELECT a.*, ISNULL(bcnt, 0) bcnt
  FROM TableA a LEFT JOIN
     (
      SELECT user_id, COUNT(1) AS BCNT
        FROM TableB
       GROUP BY user_id
     ) b
ON a.id = b.user_id

---

You can just use a LEFT JOIN and AGGREGATION functions

SELECT  b.user_id,
        min(a.username) UserName,
        min(a.email) Email,
        min(a.address) Address,
        COUNT(*) Quantity
  FROM  table_b b left join
        table_a a on a.id=b.user_id
group by b.user_id

---

select 
  *,
  (select count(*)
   from @TableB as B
   where A.id = B.user_id) as total
from @TableA as A