JOIN .... WHERE != help

[helpmeplease2]

I am trying to make it so it selects all videos that are not on a playlist. Videos are entered on a playlist in playlistvideos as either a single videoid or a range.

 

So how can I say WHERE videos.ID != playlistvideos.videoid AND videos.ID NOT BETWEEN playlistvideos.range AND playlistvideos.range2

 

SELECT playlistvideos.videoid
     , playlistvideos.range
     , playlistvideos.range2
 , videos.ID
 , videos.VTitle
 , videos.IFilename
 , videos.videoid
 , videos.GTitle
FROM playlistvideos
INNER
  JOIN videos
    ON videos.ID = playlistvideos.videoid
   OR videos.ID BETWEEN playlistvideos.range AND playlistvideos.range2
WHERE videos.GTitle = '$game' AND videos.VTitle NOT LIKE '%</a>%' ORDER BY videos.ID DESC LIMIT $offset, $rowsPerPage

[aschk]

This will give you ALL the videos that are NOT in a playlist.

 

SELECT v.ID
 , v.VTitle
 , v.IFilename
 , v.videoid
 , v.GTitle
FROM videos v
LEFT JOIN playlistvideos plv ON v.ID = plv.videoid
WHERE plv.videoid = NULL

[helpmeplease2]

That is returning 0 results however there should be some.

 

And it doesn't include the part about the range.

[aschk]

The range part is m00t because you're joining ON NULL essentially, so for each video which doesn't have a videoplaylist associated with it (i.e. LEFT JOIN appears to be NULL) you can't test a NULL range....

 

Can you supply a sample layout + data so that I can test?

 

note: change the "= NULL" to "IS NULL"

[aschk]

Quick update. I created my own tables to "mimic" what you have:

CREATE TABLE videos (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE playlist (
id INT UNSIGNED NOT NULL,
video_id INT UNSIGNED NOT NULL,
UNIQUE INDEX (id,video_id)
);

INSERT INTO videos(name) VALUES('terminator'),('robocop'),('star wars I'),
('star wars II'),('star wars III'),('terminator 2'),('pretty woman');

INSERT INTO playlist(id,video_id)
VALUES(1,1),(1,2),(1,6),(2,2),(2,3);

 

SELECT * FROM videos v
LEFT JOIN playlist p ON p.video_id = v.id
WHERE p.video_id IS NULL;

 

And it would appear it needs to be "IS NULL" not "= NULL". The latter will cause a MySQL error. The first will give you your set.

i.e. all videos that are NOT in a playlist.

 

 

[helpmeplease2]

I got it working, thanks.