1

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!.

Improve this question

1 Answer 1

Reset to default
3

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.