Two COUNTS with Left Joins are Being Multiplied

Question

I am trying to get a query that gives me the UserNames from table Users, the number of Jobs that user has from table Job2User, and the number of Places that user has from the table Place2User.

The following query keeps multiplying the value of the two count columns. For example, if for User 1 the Jobs count should be 2 and the Places count should be 4, both columns in the User 1 row will display "8". I'm not sure what I'm doing wrong:

SELECT `UserName`, COUNT(`Job2User`.`UserID`), COUNT(`Place2User`.`UserID`) 
FROM `Users`
LEFT JOIN `Job2User` ON `Job2User`.`UserID`=`Users`.`UserID` 
LEFT JOIN `Place2User` ON `Place2User`.`UserID`=`Users`.`UserID` 
GROUP BY `UserName`;

Answer 1

You should use count( distinct ...) to count unique values. Instead of counting userid ( the foreign key) count the referenced table's primary key.

See the docs here

You are getting eight because you are returning 2 records from jobs and 4 from places. Since you are not counting distinct values you get 2*4 = 8.

Answer 2

Your problem is likely that you're not mapping Place2User and Job2User tables, thus you are preforming a cross join. More info on Cross Joins

You will need to use inner queries to achieve this unless you the two tables Place2User relates to the Job2User in some way.

Try this:

SELECT `UserName`, `Job2User`.`Count`, `Place2User`.`Count` 
FROM `Users`
LEFT JOIN (SELECT `UserID`, COUNT(1) AS 'Count' FROM `Job2User` GROUP BY `UserID`) `Job2User` ON `Job2User`.`UserID`=`Users`.`UserID` 
LEFT JOIN (SELECT `UserID`, COUNT(1) AS 'Count' FROM `Place2User` GROUP BY `UserID`) `Place2User` ON `Place2User`.`UserID`=`Users`.`UserID`