'on子句'我把JOIN写错了吗? - Unknown column in 'on clause' do I have the JOIN wrong?

我在这里有这个查询，我从我们的售出门票数据库中提取事件数据…如果我删除

行，效果很好:

**IF (wp_sshow_verifys.verifyID IS NULL,TRUE,FALSE) wp_sshow_verifys**
FROM wp_sshow_sales, wp_sshow_tickets
**INNER JOIN wp_sshow_verifys ON wp_sshow_verifys.saleID=wp_sshow_sales.saleID**

此处的目标是将所有数据从下层live15表中拉出，并且仅在wp_sshow_sales+wp_sshow_tickets中从wp_sshow_sales中拉出数据。saleID存在于wp_sshow_verifys中。saleID表。

我得到这个错误:#1054 -未知列'wp_sshow_sales。saleID' in 'on子句'

我已经尝试了我能想到的一切，并寻找了一个没有让我困惑的答案，但我在这里寻求帮助。我有什么问题，从调用两个表?

SELECT 
wp_sshow_sales.saleFirstName as first,
wp_sshow_sales.saleLastName as last,
wp_sshow_sales.saleEMail as email,
wp_sshow_sales.salePPStreet as street,
wp_sshow_sales.salePPCity as city,
wp_sshow_sales.salePPState as state,
wp_sshow_sales.salePPZip as zip,
(CASE WHEN wp_sshow_tickets.priceID = 1 THEN 'Cerro Gordo' WHEN wp_sshow_tickets.priceID = 2 THEN 'Franklin' WHEN wp_sshow_tickets.priceID = 3 THEN 'Butler' WHEN wp_sshow_tickets.priceID = 4 THEN 'Hardin' WHEN wp_sshow_tickets.priceID = 5 THEN 'Floyd' WHEN wp_sshow_tickets.priceID = 6 THEN 'Mitchell' WHEN wp_sshow_tickets.priceID = 7 THEN 'Hancock' WHEN wp_sshow_tickets.priceID = 8 THEN 'Worth' WHEN wp_sshow_tickets.priceID = 9 THEN 'Wright' WHEN wp_sshow_tickets.priceID = 12 THEN 'Dickinson' WHEN wp_sshow_tickets.priceID = 13 THEN 'Howard' WHEN wp_sshow_tickets.priceID = 14 THEN 'Palo Alto' WHEN wp_sshow_tickets.priceID = 15 THEN 'Calhoun' WHEN wp_sshow_tickets.priceID = 16 THEN 'Woodbury' WHEN wp_sshow_tickets.priceID = 17 THEN 'Emmet' WHEN wp_sshow_tickets.priceID = 18 THEN 'Kossuth' WHEN wp_sshow_tickets.priceID = 19 THEN 'Winnebago' WHEN wp_sshow_tickets.priceID = 22 THEN 'Boone' WHEN wp_sshow_tickets.priceID = 23 THEN 'Buena Vista' WHEN wp_sshow_tickets.priceID = 24 THEN 'OBrien' WHEN wp_sshow_tickets.priceID = 25 THEN 'Osceola' WHEN wp_sshow_tickets.priceID = 26 THEN 'Pocahontas' WHEN wp_sshow_tickets.priceID = 27 THEN 'No County' ELSE 'Error No ID' END) AS 'County',
wp_sshow_tickets.ticketQty as qty,
IF (wp_sshow_verifys.verifyID IS NULL,TRUE,FALSE) wp_sshow_verifys
FROM wp_sshow_sales, wp_sshow_tickets
INNER JOIN wp_sshow_verifys ON wp_sshow_verifys.saleID=wp_sshow_sales.saleID
WHERE wp_sshow_sales.saleID = wp_sshow_tickets.saleID
UNION ALL
SELECT
live_15.firstname as first,
live_15.lastname as last,
live_15.email as email,
live_15.street as street,
live_15.city as city,
live_15.state as state,
live_15.zip as zip,
(CASE WHEN live_15.ticketTYPE = 1 THEN 'Cerro Gordo' WHEN live_15.ticketTYPE = 2 THEN 'Franklin' WHEN live_15.ticketTYPE = 3 THEN 'Butler' WHEN live_15.ticketTYPE = 4 THEN 'Hardin' WHEN live_15.ticketTYPE = 5 THEN 'Floyd' WHEN live_15.ticketTYPE = 6 THEN 'Mitchell' WHEN live_15.ticketTYPE = 7 THEN 'Hancock' WHEN live_15.ticketTYPE = 8 THEN 'Worth' WHEN live_15.ticketTYPE = 9 THEN 'Wright' WHEN live_15.ticketTYPE = 12 THEN 'Dickinson' WHEN live_15.ticketTYPE = 13 THEN 'Howard' WHEN live_15.ticketTYPE = 14 THEN 'Palo Alto' WHEN live_15.ticketTYPE = 15 THEN 'Calhoun' WHEN live_15.ticketTYPE = 16 THEN 'Woodbury' WHEN live_15.ticketTYPE = 17 THEN 'Emmet' WHEN live_15.ticketTYPE = 18 THEN 'Kossuth' WHEN live_15.ticketTYPE = 19 THEN 'Winnebago' WHEN live_15.ticketTYPE = 22 THEN 'Boone' WHEN live_15.ticketTYPE = 23 THEN 'Buena Vista' WHEN live_15.ticketTYPE = 24 THEN 'OBrien' WHEN live_15.ticketTYPE = 25 THEN 'Osceola' WHEN live_15.ticketTYPE = 26 THEN 'Pocahontas' WHEN live_15.ticketTYPE = 27 THEN 'No County' ELSE 'Error No ID' END) AS 'County',
live_15.qty as qty
FROM live_15

修复我之前的混淆后，我现在得到:#1222 -使用的SELECT语句有不同的列数

SELECT 
wp_sshow_sales.saleFirstName as first,
wp_sshow_sales.saleLastName as last,
wp_sshow_sales.saleEMail as email,
wp_sshow_sales.salePPStreet as street,
wp_sshow_sales.salePPCity as city,
wp_sshow_sales.salePPState as state,
wp_sshow_sales.salePPZip as zip,
(CASE WHEN wp_sshow_tickets.priceID = 1 THEN 'Cerro Gordo' WHEN wp_sshow_tickets.priceID = 2 THEN 'Franklin' WHEN wp_sshow_tickets.priceID = 3 THEN 'Butler' WHEN wp_sshow_tickets.priceID = 4 THEN 'Hardin' WHEN wp_sshow_tickets.priceID = 5 THEN 'Floyd' WHEN wp_sshow_tickets.priceID = 6 THEN 'Mitchell' WHEN wp_sshow_tickets.priceID = 7 THEN 'Hancock' WHEN wp_sshow_tickets.priceID = 8 THEN 'Worth' WHEN wp_sshow_tickets.priceID = 9 THEN 'Wright' WHEN wp_sshow_tickets.priceID = 12 THEN 'Dickinson' WHEN wp_sshow_tickets.priceID = 13 THEN 'Howard' WHEN wp_sshow_tickets.priceID = 14 THEN 'Palo Alto' WHEN wp_sshow_tickets.priceID = 15 THEN 'Calhoun' WHEN wp_sshow_tickets.priceID = 16 THEN 'Woodbury' WHEN wp_sshow_tickets.priceID = 17 THEN 'Emmet' WHEN wp_sshow_tickets.priceID = 18 THEN 'Kossuth' WHEN wp_sshow_tickets.priceID = 19 THEN 'Winnebago' WHEN wp_sshow_tickets.priceID = 22 THEN 'Boone' WHEN wp_sshow_tickets.priceID = 23 THEN 'Buena Vista' WHEN wp_sshow_tickets.priceID = 24 THEN 'OBrien' WHEN wp_sshow_tickets.priceID = 25 THEN 'Osceola' WHEN wp_sshow_tickets.priceID = 26 THEN 'Pocahontas' WHEN wp_sshow_tickets.priceID = 27 THEN 'No County' ELSE 'Error No ID' END) AS 'County',
wp_sshow_tickets.ticketQty as qty,
IF (wp_sshow_verifys.verifyID IS NULL,TRUE,FALSE) verify
FROM wp_sshow_sales
INNER JOIN wp_sshow_tickets
  ON wp_sshow_sales.saleID = wp_sshow_tickets.saleID
INNER JOIN wp_sshow_verifys 
  ON wp_sshow_verifys.saleID=wp_sshow_sales.saleID
UNION ALL
SELECT
live_15.firstname as first,
live_15.lastname as last,
live_15.email as email,
live_15.street as street,
live_15.city as city,
live_15.state as state,
live_15.zip as zip,
(CASE WHEN live_15.ticketTYPE = 1 THEN 'Cerro Gordo' WHEN live_15.ticketTYPE = 2 THEN 'Franklin' WHEN live_15.ticketTYPE = 3 THEN 'Butler' WHEN live_15.ticketTYPE = 4 THEN 'Hardin' WHEN live_15.ticketTYPE = 5 THEN 'Floyd' WHEN live_15.ticketTYPE = 6 THEN 'Mitchell' WHEN live_15.ticketTYPE = 7 THEN 'Hancock' WHEN live_15.ticketTYPE = 8 THEN 'Worth' WHEN live_15.ticketTYPE = 9 THEN 'Wright' WHEN live_15.ticketTYPE = 12 THEN 'Dickinson' WHEN live_15.ticketTYPE = 13 THEN 'Howard' WHEN live_15.ticketTYPE = 14 THEN 'Palo Alto' WHEN live_15.ticketTYPE = 15 THEN 'Calhoun' WHEN live_15.ticketTYPE = 16 THEN 'Woodbury' WHEN live_15.ticketTYPE = 17 THEN 'Emmet' WHEN live_15.ticketTYPE = 18 THEN 'Kossuth' WHEN live_15.ticketTYPE = 19 THEN 'Winnebago' WHEN live_15.ticketTYPE = 22 THEN 'Boone' WHEN live_15.ticketTYPE = 23 THEN 'Buena Vista' WHEN live_15.ticketTYPE = 24 THEN 'OBrien' WHEN live_15.ticketTYPE = 25 THEN 'Osceola' WHEN live_15.ticketTYPE = 26 THEN 'Pocahontas' WHEN live_15.ticketTYPE = 27 THEN 'No County' ELSE 'Error No ID' END) AS 'County',
live_15.qty as qty
FROM live_15

让列计数:首先选择:第一个、最后一个、电子邮件、街道、城市、州、邮政编码、价格id、数量、验证Second select: first, last, email, street, city, state, zip, priceID, qty

我的…我有一个新列叫做verify，它很丑，我不想要它

不要混用连接符号。去掉where子句，用内部连接替换，并将where移动到on子句

SELECT 
    wp_sshow_sales.saleFirstName as first,
    wp_sshow_sales.saleLastName as last,
    wp_sshow_sales.saleEMail as email,
    wp_sshow_sales.salePPStreet as street,
    wp_sshow_sales.salePPCity as city,
    wp_sshow_sales.salePPState as state,
    wp_sshow_sales.salePPZip as zip,
    (CASE WHEN wp_sshow_tickets.priceID = 1 THEN 'Cerro Gordo' 
          WHEN wp_sshow_tickets.priceID = 2 THEN 'Franklin' 
          WHEN wp_sshow_tickets.priceID = 3 THEN 'Butler' 
          WHEN wp_sshow_tickets.priceID = 4 THEN 'Hardin' 
          WHEN wp_sshow_tickets.priceID = 5 THEN 'Floyd' 
          WHEN wp_sshow_tickets.priceID = 6 THEN 'Mitchell' 
          WHEN wp_sshow_tickets.priceID = 7 THEN 'Hancock' 
          WHEN wp_sshow_tickets.priceID = 8 THEN 'Worth' 
          WHEN wp_sshow_tickets.priceID = 9 THEN 'Wright' 
          WHEN wp_sshow_tickets.priceID = 12 THEN 'Dickinson' 
          WHEN wp_sshow_tickets.priceID = 13 THEN 'Howard' 
          WHEN wp_sshow_tickets.priceID = 14 THEN 'Palo Alto' 
          WHEN wp_sshow_tickets.priceID = 15 THEN 'Calhoun' 
          WHEN wp_sshow_tickets.priceID = 16 THEN 'Woodbury' 
          WHEN wp_sshow_tickets.priceID = 17 THEN 'Emmet' 
          WHEN wp_sshow_tickets.priceID = 18 THEN 'Kossuth' 
          WHEN wp_sshow_tickets.priceID = 19 THEN 'Winnebago' 
          WHEN wp_sshow_tickets.priceID = 22 THEN 'Boone' 
          WHEN wp_sshow_tickets.priceID = 23 THEN 'Buena Vista' 
          WHEN wp_sshow_tickets.priceID = 24 THEN 'OBrien' 
          WHEN wp_sshow_tickets.priceID = 25 THEN 'Osceola' 
          WHEN wp_sshow_tickets.priceID = 26 THEN 'Pocahontas' 
          WHEN wp_sshow_tickets.priceID = 27 THEN 'No County' 
          ELSE 'Error No ID' END) AS 'County',
    wp_sshow_tickets.ticketQty as qty,
    IF (wp_sshow_verifys.verifyID IS NULL,TRUE,FALSE) wp_sshow_verifys2
    FROM wp_sshow_sales
    INNER JOIN wp_sshow_tickets
      ON wp_sshow_sales.saleID = wp_sshow_tickets.saleID
    INNER JOIN wp_sshow_verifys 
      ON wp_sshow_verifys.saleID=wp_sshow_sales.saleID
...

您的最后一列与一个表共享一个名称，别名不同，如Abhik Chakraborty所评论的。

和一个注释，你真的没有一个表来将priceID转换为值吗?如果数据存在于这样的表中，那么使用外部连接就简单得多。因为任何时候一个新的priceID进来，你必须修改这个查询…从长远来看，我认为这是一个糟糕的设计。

-------------- 解决遇到的新的错误——

支持将if语句添加到第一个查询中，现在有10列…

您需要在第二个查询中添加一列。

你至少可以做…因此，列数匹配，但也许您需要在第二个查询中执行类似的if语句以获得结果。

live_15.qty as qty, Null
FROM live_15