mysql full join error-#1054 - Unknown column 'feesmaster.PAIDAMOUNT' in 'field list'

Question

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

please view the query,

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

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 - Unknown column 'feesmaster.PAIDAMOUNT' in 'field list'

I am using phpmyadmin.

Please reformat your question and post the structure of the tables you taks about. — Matteo Tassinari, Jan 15 '13 at 08:33
@AndriusNaruševičius left outter join returns same rows as inner join — Abhiram, Jan 15 '13 at 08:36
Could you add to question your query that returns this error. — valex, Jan 15 '13 at 08:38
@valex "If i replace inner to full", I assume only one word changes :) — Andrius Naruševičius, Jan 15 '13 at 08:40

valex · Accepted Answer · 2013-01-15T08:58:13.867

3

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

edited Jan 15 '13 at 08:58

answered Jan 15 '13 at 08:44

valex

23,966
7
43
60

can u please write in the form of query? – Abhiram Jan 15 '13 at 08:48

score 0 · Answer 2 · answered Jan 15 '13 at 08:41

0

Your error is an unknown column, which basically means that in feesmaster table there is no PAIDAMOUNT column (maybe a typo?).

answered Jan 15 '13 at 08:41

Cynical

9,328
1
15
30

In the example at the very beginning of the question there is an example of how the results look when INNER JOIN is used and SUM(feesmaster.PAIDAMOUNT) is in the column list. – Andrius Naruševičius Jan 15 '13 at 08:43
All he does is changes from INNER to FULL, no other edits are made. – Andrius Naruševičius Jan 15 '13 at 08:45

mysql full join error-#1054 - Unknown column 'feesmaster.PAIDAMOUNT' in 'field list'

2 Answers2

Linked