SQL query on address referenced 2 different ways

Firstly, I didn’t design this database… just looking to do a query on it! :slight_smile:

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.


| id |name|primaryaddrid |
|1   |bill|1             |


|id|userid|addrid   |
|1 |1     |2        |
|2 |1     |3        |


|1 |1 main st|usa    |
|2 |1 main st|italy  |
|3 |1 main st|peru   |

What are you starting from, userID or name?

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.

     , name
  FROM user
  JOIN address
    ON address.id = user.primaryaddrid 
   AND address.country = 'italy'
 WHERE user.name = 'Bill'
UNION ALL          
     , name
  FROM user
  JOIN useraddresses
    ON useraddresses.userid = user.id
  JOIN address
    ON address.id = useraddresses.addrid 
   AND address.country = 'italy'
 WHERE user.name = 'Bill'
1 Like

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?

yes sir

because that’s somethiing you might wanna know

@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?

ooh, ooh, can I, Rudy, can I? :stuck_out_tongue:

“What happened when you tested it?” (™, @r937)



i love you, man

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.