MySQL查询语法帮助:错误#1066-表格/别名不唯一 [英] Help with MySQL Query syntax: ERROR #1066 - Not unique table/alias

问题描述

我有四个表user，user_billingprofile，user_shippingprofile和user_address.

用户:userId，dateCreated
user_billingprofile :用户ID，地址
user_shipping配置文件: userId，地址
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`

我收到错误:#1066 - Not unique table/alias: 'user_address'. 有没有一种方法可以进行简单的联接，即在同一查询中两次访问一个表，并将两个结果分开?最好使用某种表前缀...

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

推荐答案

可以发布表的结构吗?根据您的查询，我会说您需要考虑对其进行一些更改.

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`

请注意我添加的AS子句.您可能还需要为列添加别名(而不是SELECT *，您可能需要SELECT user_shipping_address.address AS user_shipping_address_value, user_billing_address.address AS user_billing_address_value ...)

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

希望有帮助！

这篇关于MySQL查询语法帮助:错误#1066-表格/别名不唯一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！