CODEWITHSUNDEEP
mysql
StevieD
StevieD

Reputation: 7433

How to join on a row with max value

I have three tables: households, voters, door_knocks

Each household can have several voters associated with it. Each household can also have several door knocks associated with it.

I'm trying to pull together all the voters in a household and the date of the last door_knock from the door_knocks table and I'm having trouble figuring out the proper query syntax. Here is my latest attempt:

SELECT households.hh_id, voters.id
FROM households
INNER JOIN voters ON households.hh_id = voters.hh_id
INNER JOIN ( SELECT MAX(dk.date), dk.hh_id FROM door_knocks dk GROUP BY dk.date) dks
ON dks.hh_id = households.hh_id
WHERE households.street = ?

The above query pulls up one result for each door knock, however. I just want the the date from the last door knock.

Upvotes: 1

Views: 26

Answers (1)

Sam Hartman
Sam Hartman

Reputation: 6489

So, what it sounds like you're hoping for conceptually is a table that lists the last date of a knock for each houshold. You'd like to join against that table and combine it with the voters and the households.

what your query does is give you a table of all the dates (group by dk.date) and for each date list all the households.

If you group by hh_id instead, then you will get the max date for each given household.

SELECT households.hh_id, voters.id, dks.max_date
FROM households
INNER JOIN voters ON households.hh_id = voters.hh_id
INNER JOIN ( SELECT MAX(dk.date) as max_date, dk.hh_id FROM door_knocks dk GROUP BY dk.hh_id dks
ON dks.hh_id = households.hh_id
WHERE households.street = ?

Upvotes: 1

Related Questions