All geek questions in one place

Unknown MySQL post in article

The first request works:

SELECT video.seller from video;

The second is 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 the "on" section

+4
source share
2 answers

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 problem is that the video columns are not understood in the rest of the from clause due to,. This is how the comma works. Interestingly, it can be eliminated simply by replacing it with the logical equivalent of 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 has priority. A comma has a lower priority than cross join . Thus, the connections in your expression are evaluated as "join video (type join user)". Since the second join is interpreted first, the columns in the video not known.

However, there is really a connection on the table, so it’s better to write how:

 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
+3
source

As already written, you are trying to combine the type and user tables with the condition that video.seller = user.id Since video.seller contains the bottom column of the type or user table, you have an error.

If you change the following error, this error will be resolved:

  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
+1
source

Source: https://habr.com/ru/post/1487173/

More articles:


All Articles