文章/答案/技术大牛

发布

问Mysql数据库查询
EN

Stack Overflow用户

提问于 2013-01-06 06:23:45

回答 3查看 66关注 0票数 0

我想在三个表中从mysql中获取数据。我正在使用LEFT OUTER JOIN。这是我的疑问：

SELECT cms_addresses.address1 as add1, 
  cms_addresses.email as ademail, 
  cms_addresses.city as adcity, 
  cms_addresses.clientid 
  cms_addresses.country as adcountry, 
  cms_addresses.province as adprovince, 
  cms_addresses.postal as adprovince, 
  cms_delivery_info.d_address1, 
  cms_delivery_info.d_city, 
  cms_delivery_info.d_country, 
  cms_delivery_info.d_province, 
  cms_delivery_info.d_postal, 
  cms_orders.id, 
  cms_orders.cdate, 
  cms_orders.message 
FROM cms_addresses 
LEFT OUTER JOIN cms_delivery_info 
  ON cms_addresses.clientid = cms_delivery_info.d_clientid 
LEFT OUTER JOIN cms_orders = cms_addresses.clientid = cms_orders.clientid
WHERE cms_orders.id = 10
  and cms_addresses.addresstypeid = 1

问题是这个查询没有运行，并给出了这个错误。

非唯一表/别名：'cms_addresses‘

请帮我解决这个问题。谢谢

php

mysql

sql

database

回答 3

Stack Overflow用户

发布于 2013-01-06 06:26:05

在第二个左联接中出现了一个语法错误，其中表名TRy后面有'=‘，如下所示：

SELECT 
cms_addresses.address1 as add1, 
cms_addresses.email as ademail, 
cms_addresses.city as adcity, 
cms_addresses.clientid, 
cms_addresses.country as adcountry, 
cms_addresses.province as adprovince, 
cms_addresses.postal as adprovince, 
cms_delivery_info.d_address1, 
cms_delivery_info.d_city, 
cms_delivery_info.d_country, 
cms_delivery_info.d_province, 
cms_delivery_info.d_postal, 
cms_orders.id, 
cms_orders.cdate, 
cms_orders.message 

FROM cms_addresses 

LEFT OUTER JOIN cms_delivery_info ON cms_addresses.clientid = cms_delivery_info.d_clientid 
LEFT OUTER JOIN cms_orders on cms_addresses.clientid = cms_orders.clientid 
WHERE cms_orders.id = 10 and cms_addresses.addresstypeid = 1

票数 0

Stack Overflow用户

发布于 2013-01-06 06:26:38

在cms_addresses.clientid和cms_addresses.country AS adcountry之间加一个逗号，看看它是否有效

SELECT  cms_addresses.address1 AS add1,
        cms_addresses.email AS ademail,
        cms_addresses.city AS adcity,
        cms_addresses.clientid,                  // add comma here
        cms_addresses.country AS adcountry,
        cms_addresses.province AS adprovince,
        cms_addresses.postal AS adprovince,
        cms_delivery_info.d_address1,
        cms_delivery_info.d_city,
        cms_delivery_info.d_country,
        cms_delivery_info.d_province,
        cms_delivery_info.d_postal,
        cms_orders.id,
        cms_orders.cdate,
        cms_orders.message
FROM    cms_addresses
        LEFT JOIN cms_delivery_info
                ON cms_addresses.clientid = cms_delivery_info.d_clientid
        LEFT JOIN cms_orders 
                // use ON instead of = (equal sign) here
                ON cms_addresses.clientid = cms_orders.clientid
WHERE   cms_orders.id = 10 AND cms_addresses.addresstypeid = 1

票数 0

Stack Overflow用户

发布于 2013-01-06 06:40:49

在您的SELECT中，在某种程度上：

cms_addresses.clientid cms_addresses.country as adcountry,

你好像在某个地方错过了一个,？

在您的FROM中有：

LEFT OUTER JOIN cms_orders = cms_addresses.clientid = cms_orders.clientid

同样，我猜这是一个错误(第一个=应该是一个ON)。

您收到的消息意味着您正在重用JOIN链中的表cms_addresses，并且没有给它一个别名，这对于消除歧义是必须的，但在您的示例中，这很奇怪，因为在FROM子句中没有不止一次使用cms_addresses。

我会尝试重写该查询，为所有表提供别名。还启用完整的日志记录，并检查真正到达Mysql的内容。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/14179881

复制

相似问题

问Mysql数据库查询
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Mysql数据库查询EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Mysql数据库查询
EN