CODEWITHSUNDEEP
mysqlsql
Scarface
Scarface

Reputation: 3913

Search left join null

I have an sql statement for mysql that matches a string to words in two databases. I want to exclude results that are also in another table, but when I add the left join of conv_progress, it breaks the statement. Anyone have any ideas how to exclude entries that are in another table?

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 ?, ?

Broken

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 ?, ?

Upvotes: 0

Views: 222

Answers (2)

ain
ain

Reputation: 22759

You can also use NOT EXISTS instead of join

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 ?, ?

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65557

Move conv_progress.vid_id is null into the WHERE clause:

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 ?, ?

Upvotes: 1

Related Questions