CODEWITHSUNDEEP
mysqlsql
Jerome Ansia
Jerome Ansia

Reputation: 6884

MySQL Unknown column on clause

The first query is working :

SELECT video.seller from video;

The second not:

SELECT SQL_CALC_FOUND_ROWS *,
       t.name as tname
FROM video, type as t 
INNER JOIN user 
ON video.seller = user.id 
WHERE video.type2 = t.id 
AND thumbnail_blobKey != '' 
AND disabled = 0 
AND is_reel = 0 
AND price!= 0 
AND video.type != 4  
GROUP BY video.id 
ORDER BY video.id DESC LIMIT 0, 10

Error : Unknown column 'video.seller' in 'on clause'

Upvotes: 4

Views: 195

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

This is an interesting example. Better formatted:

SELECT SQL_CALC_FOUND_ROWS *, t.name as tname
FROM video, type as t INNER JOIN
     user
     ON video.seller = user.id
WHERE video.type2 = t.id AND
     thumbnail_blobKey != '' AND disabled = 0 AND is_reel = 0 AND price!= 0 AND video.type != 4 
GROUP BY video.id
ORDER BY video.id DESC
LIMIT 0, 10

The issue is the the columns of video are not understood in the rest of the from clause because of the ,. This is how the comma works. Interestingly, it can be fixed just by replacing the , with its logical equivalent cross join:

SELECT SQL_CALC_FOUND_ROWS *, t.name as tname
FROM video cross join type as t INNER JOIN
     user
     ON video.seller = user.id
WHERE video.type2 = t.id AND
     thumbnail_blobKey != '' AND disabled = 0 AND is_reel = 0 AND price!= 0 AND video.type != 4 
GROUP BY video.id
ORDER BY video.id DESC
LIMIT 0, 10

This is explained in the documentation and the reason is precedence. The comma has lower precedence than cross join. So the joins in your expression are evaluated as "video join (type join user)". Because the second join is interpreted first, the columns in video are not known.

However, there is really a join on the table, so it is better written as:

SELECT SQL_CALC_FOUND_ROWS *, t.name as tname
FROM video join
     type t
     on video.type2 = t.id INNER JOIN
     user
     ON video.seller = user.id
WHERE thumbnail_blobKey != '' AND disabled = 0 AND is_reel = 0 AND price!= 0 AND video.type != 4 
GROUP BY video.id
ORDER BY video.id DESC
LIMIT 0, 10

Upvotes: 3

Menelaos
Menelaos

Reputation: 25725

As it is written now you are trying to join tables type, and user with condition that video.seller = user.id . As video.seller is nether a column of table type or user, you have an error.

Changing to the following would remove this error:

    SELECT SQL_CALC_FOUND_ROWS *,
       t.name AS tname
FROM video
INNER JOIN USER ON video.seller = USER.id,
                                       TYPE AS t
WHERE video.type2 = t.id
  AND thumbnail_blobKey != ''
  AND disabled = 0
  AND is_reel = 0
  AND price!= 0
  AND video.TYPE != 4
GROUP BY video.id
ORDER BY video.id DESC LIMIT 0,
                             10

Upvotes: 1

Related Questions