CODEWITHSUNDEEP
mysql
user3806757
user3806757

Reputation: 21

unknown column in on clause error in mysql

I have two tables:

mysql> select * FROM borrower;
+--------+------------+---------+--------------+
| cardno | name       | address | phone        |
+--------+------------+---------+--------------+
|      1 | A          | nj,usa  | 111-222-333 |
|      2 | B          | NY,USA  | 444-555-666 |
|      3 | C          | nj,usa  | 777-888-999 |
+--------+------------+---------+--------------+
3 rows in set (0.00 sec)

mysql> select * FROM bookloans;
+--------+----------+--------+------------+------------+
| bookid | branchid | cardno | dateout    | duedate    |
+--------+----------+--------+------------+------------+
|      1 |        1 |      1 | 2014-04-01 | 2014-08-01 |
+--------+----------+--------+------------+------------+
1 row in set (0.00 sec)

cardno in bookloans is foreign key for cardno in borrower. C

Now I am trying to execute following SQL query:

mysql> select name,address from borrower full join bookloans
  on bookloans.cardno = borrower.cardno
  group by cardno having count(bookid)=0;
ERROR 1054 (42S22): Unknown column 'borrower.cardno' in 'on clause'

I don't understand the error as borrower table does have column called cardno. Any idea what I am doing wrong?

Thanks, Sunil

Upvotes: 1

Views: 129

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562280

MySQL does not support full join syntax. So the word full in this query is being interpreted as a table alias, as if you had done a query like:

... FROM borrower AS `full`
    JOIN bookloans ...

Then subsequently when you reference borrower.cardno, it's confused because it thought you wanted to refer to the borrower table using an alias full.

I know it's a really dumb bug in the MySQL parser. I logged a bug about it (http://bugs.mysql.com/bug.php?id=69858). You should click "affects me" on that bug.

UPDATE: MySQL 8.0.31 finally supports FULL as a reserved keyword. MySQL still doesn't implement full outer join, but at least you won't get surprised by this bug. If you do want to use the word full as a table alias, you must delimit it (as you would if you used any other reserved keyword as an identifier).

Re your comment:

The common way to do a full outer join in MySQL is:

SELECT ...
FROM borrower LEFT OUTER JOIN bookloans USING (cardno)
UNION
SELECT ...
FROM borrower RIGHT OUTER JOIN bookloans USING (cardno)

However, in your case, I don't think you actually need a full outer join. You're using GROUP BY COUNT(bookid) = 0 so it seems like you're looking for a left outer join where there is no matching row in bookloans:

SELECT name, address FROM borrower
LEFT OUTER JOIN bookloans USING (cardno)
WHERE bookloans.cardno IS NULL;

Upvotes: 5

Related Questions