MYSQL Inner Join with 2 ON Clauses

Question

I am trying to setup a simple database in which I have a user and store both their residential and postal address. I have 2 tables

Users

id (Primary Key)    
name (Varchar 255)
residential_id (foreign key)
postal_id (foreign key)

Address

id (primary key)
type (enum of R and P)
street (varchar 255)
suburb (varchar 255)

I am tring to do an inner join so I end up with a result-set that looks like.

id - name - residential_street - residential_suburb, postal_street, postal_suburb

I keep getting null results for the address details, I assume this is because I am getting two sets of data from the address table and there is a conflict. Is it possible to return the address fields linked to the residential ID and the postal ID at the same time?

My SQL syntax is

SELECT * FROM users 
LEFT JOIN address
ON (users.residential_id = address.id AND users.postal_id = address.id)

EDIT. As has been pointed out my DB design is rather poor and I am looking to improve it. The key thing I am trying to achieve is that I can store the details of a person along with their associated residential and postal address. I will never be looking to expand the database to include a work address for example so hopefully that cuts down the complexity of the table.

Answer 1

You're checking if the column address is equal to an id, I don't think this will be true in any case.

Answer 2

That's relatively easy. You just need to join to the address table twice.

SELECT u.id,
       u.name,
       ar.street residential_atreet,
       ar.suburb residential_suburb,
       ap.street postal_street,
       ap.suburb postal_suburb
FROM users u
LEFT JOIN address ar ON u.residential = ar.id
LEFT JOIN address ap ON u.postal = ap.id

This kind of data model is not one I personally favour. Instead I would suggest having an address type field and having a one-to-many relationship (user.id foreign key in address table).

One problem you'll face is that determining user ownership of addresses isn't strictly straightforward (in your model). Nor is finding orphaned addresses.

One suggestion: since residential and postal are foreign keys, try to name them as such (eg residential_id and postal_id) so it's clearer when reading the SQL.

Answer 3

The following assumes that

1. the "id" column in the address is the foreign key to the user table.
2. there is an addressType column in the address table that distinguishes postal from residence

What you want is:

select 
  u.*, 
  res.street residential_street,
  res.suburb residential_suburb,
  pos.street postal_street,
  pos.suburb postal_suburb
from users u
    left join address res on u.id=res.id and res.addressType='R'
    left join address pos on u.id=pos.id and pos.addressType='P'

The key here is you have to join to the address table TWICE. The address type discriminator is needed so that each join selects only the appropriate type of address. If your schema is different, please clarify and I'll modify my answer.

Answer 4

@m3mbran3 my suggestion would be to dump the residential_id & postal_id fields from Users

Users

id (Primary Key)    
name (Varchar 255)

Address

id (if required, otherwise primary key is combo of userid,type)
user_id
type (enum of R and P)
street (varchar 255)
suburb (varchar 255)

Then you're back to

SELECT u.id,
       u.name,
       ar.street residential_street,
       ar.suburb residential_suburb,
       ap.street postal_street,
       ap.suburb postal_suburb
FROM users u
LEFT JOIN address ar 
           ON u.id = ar.user_id
           AND ar.type = 'R'
LEFT JOIN address ap 
           ON u.id = ap.user_id
           AND ap.type = 'P'

a variation of @Cletus suggestion. Remembering that if a user has a residential address and no postal address or vice/versa there may be nulls