Two COUNTS with Left Joins are Being Multiplied

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 P开发者_JS百科laces 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`;

---

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.

---

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` 

---

One option is to use inline views for each table you want to count

SELECT `UserName`, `Job2User`.`Job2UserCount`, `Place2User`.`Place2UserCount`
FROM `Users`
 ` 
LEFT JOIN
(SELECT

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

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

---

The first issue I see is that you have the joins backwards. You are doing left joins but the Users table is on the right of that join. You need to switch that around like so:

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

Since you are doing the count on the fields that will be NULL when they aren't linked, this might work (sorry it is late and my brain is a little fuzzy). If it doesn't, here is a way you could do it:

SELECT UserName, 
  (SELECT COUNT(Job2User.UserID) 
   FROM Job2User 
   WHERE Job2User.UserID = t.UserID) AS Job2UserCount,
  (SELECT COUNT(Place2User.UserID) 
   FROM Place2User 
   WHERE Place2User.UserID = t.UserID) AS Place2UserCount
FROM Users AS t

---

Well its simple for each Job2User ure getting all Place2user its why its multipyed. U should count it in subquery then use left join.