Firstly, I didn’t design this database… just looking to do a query on it!
As you can see in the tables below (edited for simplicity) - the User table has primaryaddrid, however this isn’t included in the addresses listed in UserAddresses. So if the query was to check if a user has an address in a particular country, it would need to look at both references.
Is doing a union the most efficient way to overcome this odd situation? I’m wanting to do other conditions outside of the address check, an example would be checking the name and address.
Edit: Probably should have clarified - primaryaddrid is a FK to the Address table.
User
| id |name|primaryaddrid |
|----|----|--------------|
|1 |bill|1 |
I might not be understanding the question. Starting from User table I suppose, and obtain all users that have an address in italy with a name bill, as an example.
SELECT id
, name
FROM user
INNER
JOIN address
ON address.id = user.primaryaddrid
AND address.country = 'italy'
WHERE user.name = 'Bill'
UNION ALL
SELECT id
, name
FROM user
INNER
JOIN useraddresses
ON useraddresses.userid = user.id
INNER
JOIN address
ON address.id = useraddresses.addrid
AND address.country = 'italy'
WHERE user.name = 'Bill'
Interesting, thanks @r937. I was playing about with union and thought there might be something else I am missing.
Just one question I had, and still have, on the union solution. Will your query bring back 2 results for the same user, if say the primaryaddrid pointed to italy and one of the other addresses in UserAddresses also pointed to italy as well?
@r937 how can the query be changed so it doesn’t return multiple? Basically I’d just want to return a list of users that is named bill and is associated to an italy address? Is that a case of applying unique on the select?
