CODEWITHSUNDEEP
mysqlsqldatabase
flpn
flpn

Reputation: 1978

How to bring ALL data from a query in MySQL?

I want to write a query that can show the amount of purchases made in the month of June, grouped by city. So I wrote this query:

SELECT state, city, COUNT(*) 
FROM address 
JOIN person 
JOIN purchase
WHERE purchase.person_FK = person.id 
AND address.person_FK = person.id
AND MONTH(purchase.purchase_date) = 5
GROUP BY state, city
ORDER BY state, city;

But this query doesn't return the cities that have no purchases in that month, and I want to show them. Can you help me?

Upvotes: 1

Views: 46

Answers (3)

Matt Spinks
Matt Spinks

Reputation: 6700

You will need to have a table that provides a listing of all cities you want to show (if you don't already have that). Then you join to the city table as well. Otherwise, your query has no idea which cities to show with a zero count. In addition, you will need to change your JOIN's to LEFT JOIN's

SELECT city.state, city.city, COUNT(*) 
FROM address 
LEFT JOIN person ON person.id = address.person_FK
LEFT JOIN purchase ON purchase.person_FK = person.id
LEFT JOIN city ON purchase.city = city.city
WHERE MONTH(purchase.purchase_date) = 5
GROUP BY address.state, address.city
ORDER BY address.state, address.city;

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You need a city table with all the cities, then do a LEFT JOIN.

And put the JOIN condition on the ON section not the WHERE

SELECT Cities.state, Cities.city, COUNT(*) 
FROM  Cities
LEFT JOIN Purchase
  ON Cities.city = Purchase.city
 AND Cities.state = Cities.state
JOIN person 
  ON purchase.person_FK = person.id 
 AND MONTH(purchase.purchase_date) = 5
JOIN address 
  ON address.person_FK = person.id
GROUP BY Cities.state, Cities.city
ORDER BY Citiesstate, Cities.city;

Upvotes: 1

dbajtr
dbajtr

Reputation: 2044

Look at your joins, 'JOIN' is the same as 'INNER JOIN' which only shows results which is in both tables, you'll need to use a LEFT or FULL join to get what you need.

Theres a diagram here which explains them well

Upvotes: 1

Related Questions