I have four tables, such that:
CREATE TEMP TABLE country (
countryid serial PRIMARY KEY,
name text
);
CREATE TEMP TABLE city (
cityid serial PRIMARY KEY,
name text,
countryid int REFERENCES country
);
CREATE TEMP TABLE user_country (
userid int,
countryid int REFERENCES country
);
CREATE TEMP TABLE user_city (
userid int,
cityid int REFERENCES city
);
I'm trying to write a query which will match users up based on their user_country and user_city entries, so that any one hit will produce a match if,
- a user has any of the same
user_country
oruser_city
entries as another user. - given the relationship between city and country; if one user has an entry in the
user_country
table and another user has an entry in theuser_city
table with a city that is in the firstusers_country
, this will also produce a match.
For example, given the following example data and the user id
1
, the query will return the user ids 2
, 3
, 4
: 2
is matched in the user_country
table (both users have an entry containing the country_id
1
) and user id
s 3
and 4
are matched because their entries in the user_city
table relate to London which has a country_id
which matches user id 1
's user_country
entry. (In laymans terms, user 1
wants to go anywhere in England, so does user 2
, producing a match; users 3
and 4
are more specific but because both London and Leeds are in England they both produce matches)
Given the same data and the user id
3
, the query will return the user id 1
and 2
, as the country_id
in the city
table relates to the country that both users 1
and 2
have selected, but not user 4
. (in laymans terms, user 3
wants to go to London; users 1
and 2
want to go anywhere in England, so they match. However user 4
only wants to go to Leeds, not London, so doesn't match)
INSERT INTO country (name) VALUES ('England'),('Canada');
INSERT INTO city (name,countryid) VALUES ('London',1),('Leeds',1),('Vancouver',2);
INSERT INTO user_country (userid, countryid) VALUES (1,1),(2,1);
INSERT INTO user_city (userid, cityid) VALUES (3,1),(4,2);
Can anyone help me with a single performant SQL query, that given a user id
, returns a list of user ids which match the above scenarios?
Note: I'm using a recent version of postgres
EDIT
To clarify a few points: a user can have an entry in the user_city
table without a corresponding entry in the user_country
table for the country matching the city. This is because a user may have an interest in visiting a city, but not want to see the rest of the country. Being able to select a country is a shorthand way of selecting every single city in that country.
In layman's terms: just because a user wants to visit London, they don't necessarily want to visit all of the UK. So a user can have a user_city entry for London, but not have a user_country entry for the UK; also worth noting that if a user has a user_country entry for the UK, they won't have any user_city entries for any cities in that country (user wants to visit all of the UK, so doesn't make sense to list London too).
Separately a city will always be in a country, so will always have a country_id
EDIT 2 A bit more example data to replace the data above (not in addition to!)
INSERT INTO country (name) VALUES ('Germany'),('Spain'),('Italy');
INSERT INTO city (name,countryid) VALUES ('Berlin',1),('Madrid',2),('Rome',3),('Venice',3);
INSERT INTO user_country (userid, countryid) VALUES (1,1),(2,1),(5,3)
INSERT INTO user_city (userid, cityid) VALUES (3,2),(4,2),(6,3),(7,4)
Testing a match only on matching user_country table entries:
- Entering user id 1 returns user id 2
- Entering user id 2 returns user id 1
Testing a match only on matching user_city table entries:
- Entering user id 3 returns user id 4
- Entering user id 4 returns user id 3
Testing that a user_country matches all entries with related cities:
- Entering user id 5 returns user ids 6 & 7
Testing that a user_city entry picks up a user_country entry:
- Entering user id 6 returns user id 5
- Entering user id 7 returns user id 5
user_city
does not have auser_country
.. That's kind of why I asked. Anyway, it's only the diff between an inner join, and a left join in my query.user_city
entry for London, but not have auser_country
entry for the UK; also worth noting that if a user has auser_country
entry for the UK, they won't have anyuser_city
entries for any cities in that country (user wants to visit all of the UK, so doesn't make sense to list London too). Separately acity
will always be in acountry
, so will always have acountry_id