mysql two table query problem

Question

a table
a_id a_value
 1   text1
 2   test2

b table
b_id b_num a_id 
 1    5     1
 2    7     1
 3    2     1
 4    7     2
 5    56    2

Results base a table (edited)

a_id:1 a_value:text1 total:3 records

a_id:2 a_value:text2 total:2 records

How can get this format in sql?

query a table and add a field(total) count b.a_id = a.a_id in table b

thank you..

Answer 1

I imagine you have an error in your b table, so I will assume what you call b_id is actually a_id, or your results would be wrong

Anyway you could use:

SELECT COUNT(b.a_id) AS total FROM b GROUP BY (SELECT a.a_id FROM a)
   ORDER BY b.a_id 

Answer 2

The updated query based on changes to the question

SELECT a_id, a_value, x.total
FROM a
INNER JOIN 
    (SELECT b.a_id, COUNT(1) AS total 
    FROM b 
    GROUP BY (b.a_id)) X
ON a.a_id = x.a_id
ORDER BY a.a_id

Answer 3

You can try:

SELECT a.a_id AS id, a.a_value AS value, (SELECT count(b.b_id) AS count FROM b WHERE (b.a_id = a.a_id)) AS total FROM a GROUP BY a.a_id

Then the result for your example using the data from tables a and b:

**id    value     total** 

  1     text1     3

  2     text2     2