Help with MySQL Query syntax: ERROR #1066 - Not un

Question

I have four tables, user, user_billingprofile, user_shippingprofile, and user_address.

user: userId, dateCreated
user_billingprofile: userId, address
user_shippingprofile: userId, address
user_address: random address crap

Here is the query I have to get a users billing and shipping profiles in one shot.

SELECT * FROM `user`
  JOIN `user_billingprofile`  ON `user`.`userId` = `user_billingprofile`.`userId`
    JOIN `user_address` ON `user_billingprofile`.`currentAddress` = `user_address`.`addressId`
  JOIN `user_shippingprofile` ON `user_shippingprofile`.`currentAddress` = `user_address`.`addressId`
    JOIN `user_address` ON `user_shippingprofile`.`currentAddress` = `user_address`.`addressId`

I get the error: #1066 - Not unique table/alias: 'user_address'. Is there a way to take a simple join where a table is accessed twice in the same query, and separate the two results? Preferably with some kind of table prefix...

I'm a bit lost here. I know I could do this in two sepparate queries quite easily, but i'd like to learn how to do stuff like this in one shot.

Any help/suggestions/direction is greatly appreciated, thank you!.

Answer 1

Can you post the structure of your tables? Based on your query I'd say you need to consider changing it up a bit.

That said you can fix your current query by adding a table alias like so:

SELECT * FROM `user`
  JOIN `user_billingprofile`  ON `user`.`userId` = `user_billingprofile`.`userId`
    JOIN `user_address` AS user_billing_address ON `user_billingprofile`.`currentAddress` = `user_address`.`addressId`
  JOIN `user_shippingprofile` ON `user_shippingprofile`.`currentAddress` = `user_address`.`addressId`
    JOIN `user_address` AS user_shipping_address ON `user_shippingprofile`.`currentAddress` = `user_address`.`addressId`

Note the AS clause I added. You'll probably need to alias the columns too (instead of SELECT * you likely will need SELECT user_shipping_address.address AS user_shipping_address_value, user_billing_address.address AS user_billing_address_value ... )

Hope that helps!