mysql full join error-#1054-“字段列表"中的未知列"feesmaster.PAIDAMOUNT"

问题描述

我有查询返回这样的数据

I have query which returns the data like this

DATE( expensemaster.`date` )    SUM( feesmaster.PAIDAMOUNT )    SUM( expensemaster.amount )
2013-01-09                         0                             7824.4
2013-01-15                         200                            211

请查看查询，

SELECT DATE( expensemaster.`date` ) , 
       SUM( feesmaster.PAIDAMOUNT ) , 
       SUM( expensemaster.amount ) 
FROM 
    feesmaster
    INNER JOIN expensemaster 
        ON DATE( feesmaster.DATETIME ) = DATE( expensemaster.date ) 
WHERE 
    DATE( expensemaster.`date` ) BETWEEN  '2013-01-09' AND  '2013-01-15'
    AND DATE( feesmaster.`datetime` ) BETWEEN  '2013-01-09' AND '2013-01-15'
GROUP BY 
    DATE( feesmaster.`datetime` ), 
    DATE( expensemaster.`date` )

如果我将inner替换为full，那么当其中一张表中存在匹配项时，我会得到Return行，但是我遇到了错误

If i replace inner to full so i can get Return rows when there is a match in one of the tables but i am getting error

#1054-字段列表"中的未知列"feesmaster.PAIDAMOUNT"

我正在使用phpmyadmin.

I am using phpmyadmin.

推荐答案

正如我所知，MySQL不支持FULL JOIN，因此在这种情况下，单词FULL表示您的第一个 ALIAS 表feesmaster.因此，除了FULL.PAIDAMOUNT之外没有列feesmaster.PAIDAMOUNT.您可以通过 UNION左联接和右联接来实现FULL JOIN

As I know MySQL doesn't support FULL JOIN so the word FULL in this case means an ALIAS for your first table feesmaster. So there is no column feesmaster.PAIDAMOUNT but FULL.PAIDAMOUNT. You can implement FULL JOIN by UNION LEFT JOIN and RIGHT JOIN

UPD:另外，在您的情况下，仅当expensemaster.date和feesmaster.datetime不是WHERE条件为TRUE时，FULL JOIN(以及LEFT/RIGHT JOIN的)也没有意义. NULL.因此，在这种情况下，只有INNER JOIN起作用.

UPD: Also in your case FULL JOIN (and LEFT/RIGHT JOIN's also) has no sense for this query as soon your WHERE conditions TRUE only if expensemaster.date and feesmaster.datetime aren't NULL. So only INNER JOIN works in this case.

这篇关于mysql full join error-#1054-“字段列表"中的未知列"feesmaster.PAIDAMOUNT"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！