I am using Postgres 13 and have the following tables (tables column are truncated for clarity) essentially a join table (location) between person and address with extra columns and it's own primary key (this I cannot change).
Person table
person_id
-----------
1
2
Location table
location_id | status | address_id | person_id
-------------+--------+------------+-----------
1 | 1 | 1 | 1
2 | 1 | 2 | 1
3 | 1 | 3 | 2
4 | 1 | 4 | 2
Address table
address_id | postcode
------------+----------
1 | NR1
2 | NR2
3 | NR3
4 | NR3
What I would like to achieve is a query that will fetch all person ids that have addresses with more than one postcode and a location status of 1. The person table has over 10 million records and each person can have multiple addresses so I would like to use limit and offset so I can get the data via multiple queries.
The result of the sample data in the tables would just return person_id 1 as it has two different postcodes (NR1 & NR2), whereas person_id 2 has two identical postocdes (NR3 & NR3)
My first attempt without paging is this which seems to be working, but I am sure there are better ways of doing it
SELECT sq.person_id
FROM
(
SELECT
p.person_id,
COUNT(DISTINCT a.postcode) AS count_postcode
FROM person p
INNER JOIN location l
ON p.person_id = l.person_id
INNER JOIN address a
ON a.address_id = l.address_id
WHERE l.status = 1
GROUP BY p.person_id
) sq
WHERE sq.count_postcode > 1;
You can join your tables and do a distinct count on the Postcode column, grouped per person:
select l.person_id
from location l
join address a on a.address_id = l.address_Id
group by l.person_id
having Count(distinct a.postcode) > 1;