Select distinct record with join count records

Question

I have two tables: Company and Contact, with a relationship of one-to-many. I have another table Track which identifies some of the companies as parent companies to other companies.

I want to write a SQL query that selects the parent companies from Track and the amount of contacts that each parent has.

SELECT Track.ParentId, Count(Contact.companyId) 
FROM Track     
    INNER JOIN Contact
    ON Track.ParentId = Contact.companyId
GROUP BY Track.ParentId

however The result holds less records than when I run the following query:

SELECT DISTINCT Track.ParentId
FROM Track

I tried the first query with an added DISTINCT and it returned the same results (less then what it was meant to).

read https://mattmazur.com/2017/11/01/counting-in-mysql-when-joins-are-involved/ — Tokci, Feb 04 '21 at 08:14

Tom H · Answer 1 · 2016-01-11T16:08:37.640

4

You're performing an INNER JOIN with the Contact table, which means that any rows from the first table (Track in this case) with no matches to the JOINed table will not show up in your results. Try using a LEFT OUTER JOIN instead.

The COUNT with Contact.companyId will only count rows where there is a match (Contact.companyId is not NULL). Since you're counting contacts that's fine as they will count as 0. If you were trying to count some other set of data and tried to do a COUNT on a specific column (rather than COUNT(*)) then any NULL values in that column would not count towards your total, which might or might not be what you want.

edited Jan 11 '16 at 16:08

answered Jan 11 '16 at 14:59

Tom H

46,766
14
87
128

1

Looks good, in addition however, if rosa wants 0 or null counts, may need to adjust count using coalese or case or isnull or other variations to return 0 instead of null. perhaps `coalesce(count(contact.companyID)) as cnt` – xQbert Jan 11 '16 at 15:04
Yes, depending on what the specific requirements are that might be necessary. Good point. – Tom H Jan 11 '16 at 15:08
I can't use left outer join, because I'm using access, but the answer I found is similar. Thanks. – MJH Jan 11 '16 at 15:59
@xQbert, Can any one elaborate on the 0 or null remark? I didn't understand. Remember- I'm using ms-access – MJH Jan 11 '16 at 16:02
1

I've added an explanation of COUNT with NULL values. Hopefully it's clear enough. – Tom H Jan 11 '16 at 16:08
@rosa msaccess supports [left, right joins](https://msdn.microsoft.com/en-us/library/bb208894(v=office.12).aspx) FULL outer must be [simulated](http://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access) using left and right joins along with a union . – xQbert Jan 11 '16 at 17:05

score 0 · Answer 2 · answered Jan 11 '16 at 15:57

0

I used an INNER JOIN which returns only records that are identical in both tables.

To return all records from Track table, and records that match in the Contact table, I need to use LEFT JOIN.

answered Jan 11 '16 at 15:57

MJH

839
1
17
37

Select distinct record with join count records

2 Answers2