搜索左联接空

Question

我有一个用于mysql的sql语句，它将一个字符串与两个数据库中的单词相匹配。我想排除另一个表中的结果，但是当我添加conv_progress的左联接时，它会中断语句。有人知道如何排除另一个表中的条目吗？

Works

SELECT video.*,
  MATCH(video.title) AGAINST(? IN BOOLEAN MODE) as cscore, 
  MATCH(tags.name) AGAINST(? IN BOOLEAN MODE) as htscore
FROM video
LEFT JOIN tags ON video.vid_id=tags.vid_id
null
WHERE
  MATCH(video.title) AGAINST(? IN BOOLEAN MODE) OR
  MATCH(tags.name) AGAINST(? IN BOOLEAN MODE) GROUP BY video.vid_id
ORDER BY cscore DESC LIMIT ?, ?

断裂

SELECT video.*,
  MATCH(video.title) AGAINST(? IN BOOLEAN MODE) as cscore, 
  MATCH(tags.name) AGAINST(? IN BOOLEAN MODE) as htscore
FROM video
LEFT JOIN tags ON video.vid_id=tags.vid_id
LEFT JOIN conv_progress on video.vid_id=conv_progress.vid_id where conv_progress.vid_id is null
WHERE
  MATCH(video.title) AGAINST(? IN BOOLEAN MODE) OR
  MATCH(tags.name) AGAINST(? IN BOOLEAN MODE) GROUP BY video.vid_id
ORDER BY cscore DESC LIMIT ?, ?

Answer 1

将conv_progress.vid_id is null移到WHERE子句中：

SELECT video.*,
  MATCH(video.title) AGAINST(? IN BOOLEAN MODE) as cscore, 
  MATCH(tags.name) AGAINST(? IN BOOLEAN MODE) as htscore
FROM video
LEFT JOIN tags ON video.vid_id=tags.vid_id
LEFT JOIN conv_progress on video.vid_id=conv_progress.vid_id 
WHERE
  (
    MATCH(video.title) AGAINST(? IN BOOLEAN MODE) OR
    MATCH(tags.name) AGAINST(? IN BOOLEAN MODE)
  ) 
  AND conv_progress.vid_id is null
GROUP BY video.vid_id
ORDER BY cscore DESC LIMIT ?, ?

Answer 2

您也可以使用NOT EXISTS代替联接。

SELECT video.*,
  MATCH(video.title) AGAINST(? IN BOOLEAN MODE) as cscore, 
  MATCH(tags.name) AGAINST(? IN BOOLEAN MODE) as htscore
FROM video
LEFT JOIN tags ON video.vid_id=tags.vid_id
null
WHERE
  (
  MATCH(video.title) AGAINST(? IN BOOLEAN MODE) OR
  MATCH(tags.name) AGAINST(? IN BOOLEAN MODE)
  )AND(NOT EXISTS(SELECT 1 FROM conv_progress WHERE video.vid_id=conv_progress.vid_id))
 GROUP BY video.vid_id
ORDER BY cscore DESC LIMIT ?, ?