multi-table query when there is no record in one table

Question

What should I do if I want to:

For now, there are table A and table B,

A:
id, name, address            //the id is unique
B
id, contact, email

Since one person may have more than one contact and email, or have no contact and email(which means no record in table B)

Now I want to count how many records for each id, even 0: And the result will look like:

id, name, contact_email_total_count

How can I do that(for now the only place I can not figure out is how to count 0 record since there is no record in table B)?

How is the relation between table A and B ? To me it look like if you do left join then you will be able to get the count. — DevelopmentIsMyPassion, Jan 10 '13 at 18:09

score 2 · Accepted Answer · edited May 23 '17 at 11:56

2

For that case you will want to use a LEFT JOIN, then add an aggregate and a GROUP BY:

select a.id, 
  a.name, 
  count(b.id) as contact_email_total_count
from tablea a
left join tableb b
  on a.id = b.id
group by a.id, a.name

If you need help learning join syntax here is a great visual explanation of joins.

Based on your comment the typical order of execution is as follows:

FROM

ON

JOIN

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

edited May 23 '17 at 11:56

Community

answered Jan 10 '13 at 18:07

Taryn

I wonder why it is still able to join even if there is no such an id in table b, when that join happen, it will not satisfy "on a.id = b.id" – Kuan Jan 10 '13 at 18:34
@Kuan that is the point of the `LEFT JOIN` the table on the left `tableA` will return all records even if it does not exist in `tableb`. – Taryn Jan 10 '13 at 18:36
If this answer is helpful, then be sure to accept it via the checkmark to the left of it. It helps future visitors to the site and you get rep for accepting. – Taryn Jan 10 '13 at 18:45
Thanks so much! BF. One last question is: Can you show me the execution sequence of this SQL line? I kinda need some detail to under stand what run behind this line. – Kuan Jan 10 '13 at 19:00
@Kuan see my edit. There are many articles online about the sql order of execution. – Taryn Jan 10 '13 at 19:06

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

Need to do a left join to maintain the records in table A regardless of B:

Need to aggregate the count of records in B:

edited May 23 '17 at 12:04

Community

answered Jan 10 '13 at 18:11

Eric J. Price

2 Answers2