0

I have a query that attempts to retrieve IDs of people, but only if those people have more than one address. I'm also checking that the last time I called them was at least 30 days ago. Finally, I'm trying to order the results, because I want to pull up results with the oldest last_called datetime:

SELECT 
  p.id,
  COUNT(*) AS cnt 
FROM
  people p 
  LEFT JOIN addresses a 
    ON p.id = a.id 
WHERE p.last_called <= DATE_SUB(NOW(), INTERVAL 30 DAY) 
GROUP BY p.id 
HAVING COUNT(*) > 1
ORDER BY p.last_called ASC 
LIMIT 25

Right now, the results are not excluding people with only one address. I haven't even got to the point where I know if the sort order is correct, but right now I'd just like to know why it is that my query isn't pulling up results where there is at least 2 addresses for the person.

Brian Gottier
  • 4,522
  • 3
  • 21
  • 37

1 Answers1

0

If you don't want to include people with no address then I would recommend using INNER JOIN instead of LEFT JOIN and DISTINCT to get distinct address ids (just in case if you have duplicate mappings), e.g.:

SELECT 
  p.id,
  COUNT(DISTINCT(a.id)) AS cnt 
FROM
  people p 
  JOIN addresses a 
    ON p.id= a.peopleid 
WHERE p.last_called <= DATE_SUB(NOW(), INTERVAL 30 DAY) 
GROUP BY p.id 
HAVING COUNT(DISTINCT(a.id)) > 1

As far as Ordering is concerned, MySQL evaluates GROUP BY before ordering the results and hence, you need to wrap the query inside another query to get the ordered results.

Update

Instead of joining on aid, you need to join on peopleId of an address record to get the people record.

Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • I'm not getting any results from that query. I removed the ordering so my query is just like yours, and nothing ... – Brian Gottier Mar 30 '17 at 22:57
  • I had to drop DISTINCT for it to start returning results. You had no way to know that the a.id is not primary or unique, so I think that's why you were using it. Anyways, I've got that handled. What about the sorting? Can you point me in the right direction? – Brian Gottier Mar 30 '17 at 23:10
  • http://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by – Darshan Mehta Mar 30 '17 at 23:15