How do I use SQL to search a many to many relationship using AND-CodePudding

Can anyone help to create a SQL code which could list movies which have been searched under 2 or more tags for the tables below? E.g. I want to list all movies which have the tags “4star” AND “Drama”.

Tables

I have managed to create one which lists movies which have either one or another tag… thus.

Select tblMovies.MovieName 
FROM tblMovies, tblBridge, tblTags 
WHERE ((tblTags.TagID=1) OR (tblTags.TagID=5))
And tblTags.TagID = tblBridge.TagID 
And tblBridge.MediaID= tblMovies.MovieID

Which gives Star Wars, Aliens, Goodfellows, Mermaids.

But I'm struggling with the AND code which would give Goodfellows and The Godfather if I search for movies which have tags 1 (4star) and 7 (Drama) for example.

Many thanks.

CodePudding user response：

You are looking for movies for which exist both tags 1 and 7. We don't use joins usually when we only want to check whether data exists. We use EXISTS. Or IN, which expresses the same thing (movies that are in the set of tag 1 movies and also in the set of tag 7 movies).

The idea is that we select FROM the table we want to see results from. And we use the WHERE clause to tell the DBMS which rows we want to see.

With EXISTS

SELECT m.moviename
FROM tblmovies m
WHERE EXISTS (SELECT null FROM tblbridge b WHERE b.tagid = 1 AND b.movieid = m.movieid)
  AND EXISTS (SELECT null FROM tblbridge b WHERE b.tagid = 7 AND b.movieid = m.movieid)
ORDER BY m.moviename;

With IN

SELECT m.moviename
FROM tblmovies m
WHERE m.movieid IN (SELECT b.movieid FROM tblbridge b WHERE b.tagid = 1)
  AND m.movieid IN (SELECT b.movieid FROM tblbridge b WHERE b.tagid = 7)
ORDER BY m.moviename;

I should add that these are not the only options available to get that result. But they are the straight-forward ones. (Another is conditional aggregation, but you'll learn this later.)