CODEWITHSUNDEEP
mysqljoingroup-by
DSB
DSB

Reputation: 615

MySQL JOIN + WHERE + GROUP BY

Here DB Structure:

turns DB Table
+-----------+-------------+------------+------------+----------------+
| turnNumber| userId      | locationId | status     | itemsPurchased |
+-----------+-------------+------------+------------+----------------+
| 32        | 1           | 1          | 1          | 20             |
| 33        | 2           | 1          | 0          | 0              |
+-----------+-------------+------------+------------+----------------+

locations DB Table
+-----------+---------+---------+
| id        | Address | ZIPCode | 
+-----------+---------+---------+
| 1         | ...     | 12345   | 
| 2         | ...     | 67890   | 
+-----------+---------+---------+

Im trying to get every location data (Address, ZIPCode...) + the amount of turns pending (with status 0) per location + the sum of items purchased per location (for all turns even if their state is 1)

Here my Query:

SELECT 
    l.*, 
    COUNT(t.id) AS turns,
    SUM(IF(t.itemsPurchased > 0, t.itemsPurchased, 0)) AS items
FROM turns t RIGHT OUTER JOIN locations l
ON t.locationId = l.id          
WHERE t.status = 0 AND
l.ZIPCode = XXXX 
GROUP BY l.id

The thing is when i put the t.status condition it doesnt get the location data when theres no turn with status 0 in turns table, also even if it would, i guess the count for items purchased would take in count only turns with status 0 and not all turns.

Im wondering if theres a way to get all data within the same query, please Help!

Edit:

The expected output is as following:

+-----------+-------------+------------+------------+----------------+
| id        | Address     | ZIPCode    | turns      | itemsPurchased |
+-----------+-------------+------------+------------+----------------+
| 1         | ...         | 12345      | 1          | 20             |
+-----------+-------------+------------+------------+----------------+

Upvotes: 0

Views: 209

Answers (2)

LukStorms
LukStorms

Reputation: 29647

Using a LEFT JOIN and putting the criteria on different places.

To avoid that by using a criteria in the WHERE clause for the LEFT JOIN'd table, that it would give it the effect on as it were an INNER JOIN.

SELECT 
loc.ZIPCode,
COUNT(DISTINCT CASE turns.status WHEN 0 THEN turns.id END) AS turns,
SUM(CASE 
    WHEN turns.status = 1 AND turns.itemsPurchased > 0 
    THEN turns.itemsPurchased 
    ELSE 0 
    END) AS items
FROM locations loc
LEFT JOIN turns ON turns.locationId = loc.id
WHERE loc.ZIPCode = 12345
GROUP BY loc.id, loc.ZIPCode

Upvotes: 0

spencer7593
spencer7593

Reputation: 108370

The condition "t.status = 0" in the WHERE clause negates the "outerness" of the join; the same result we'd get with an INNER JOIN.

With the outer join, any rows in locations that don't have a matching row in turns will be returned with NULL values for the all of the t. columns. The unmatched rows from locations are going to get excluded by the condition in the WHERE clause.

Consider relocating that condition from the WHERE clause to ON clause of the outer join.

Or consider relocating that condition into an aggregate expression.

As an example:

SELECT l.id
     , l.zipcode
     , SUM(IF(t.status = 0, 1, 0)) AS turns
     , SUM(IF(t.status = 0 AND t.itemspurchased > 0, t.itemspurchased, 0)) AS items
 FROM locations l

 LEFT 
 JOIN turns t
   ON t.locationid = l.id
  AND t.status     = 0 

WHERE l.zipcode = XXXX 
GROUP
   BY l.id
    , l.zipcode

Upvotes: 2

Related Questions