CODEWITHSUNDEEP
mysqlsql-server
Naveen Honest Raj K
Naveen Honest Raj K

Reputation: 362

SQL Query Not Working. Count should return zero also. But not working even after outer join

I am having some trouble to find the solution to this small problem. I wanted to get the count of teams and also I need the zero counts too. This is working if I don't mention specific teams. But failed to work when the query is made complex.

SELECT teams.name, COUNT(checkins.team_id) AS "count of checkins"
FROM checkins 
  RIGHT JOIN teams ON checkins.team_id = teams.id
GROUP BY checkins.team_id

This failed to work when it is changed like this

SELECT teams.name, COUNT(checkins.team_id) AS "count of checkins"
FROM checkins 
  RIGHT JOIN teams ON checkins.team_id = teams.id
  WHERE checkins.team_id IN (1,3,2)
GROUP BY checkins.team_id

Upvotes: 1

Views: 52

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

You can move the WHERE IN ... condition into the ON clause:

SELECT
    teams.name,
    COUNT(checkins.team_id) AS "count of checkins"
FROM checkins
RIGHT JOIN teams
    ON checkins.team_id = teams.id AND checkins.team_id IN (1,3,2)
GROUP BY checkins.team_id

The reason your latest change is changing the counts is that the WHERE clause is filtering off records before the aggregation happens. By moving everything into the ON clause, the join retains all the original records on the right side of the join going into the aggregation.

But most of the time we would write this query using a LEFT JOIN rather than a right one:

SELECT
    teams.name,
    COUNT(checkins.team_id) AS "count of checkins"
FROM teams
LEFT JOIN checkins
    ON teams.id = checkins.team_id AND checkins.team_id IN (1,3,2)
GROUP BY checkins.team_id

Upvotes: 2

Arthur Almeida
Arthur Almeida

Reputation: 568

You can try this:

SELECT teams.name, COUNT(checkins.team_id) AS "count of checkins"
FROM checkins 
  RIGHT JOIN teams ON checkins.team_id = teams.id
  WHERE (checkins.team_id IN (1,3,2) OR checkins.team_id IS NULL )
GROUP BY checkins.team_id

Upvotes: 2

Ashu
Ashu

Reputation: 482

Hope this works as intended

SELECT teams.name, COUNT(checkins.team_id) AS "count of checkins"
FROM checkins 
  RIGHT JOIN teams ON checkins.team_id = teams.id
  WHERE checkins.team_id IN ('1','3','2')
GROUP BY checkins.team_id

Upvotes: 0

Related Questions