我如何使用SQL来搜索许多与许多关系的关系和

发布于 2025-02-05 03:18:52 字数 543 浏览 1 评论 0原文

谁能帮助创建一个可以列出以下表2或更多标签搜索的电影的SQL代码？例如，我想列出所有带有标签“ 4star”和“ Drama”的电影。

我设法创建了一个列出了一个具有一个或另一个标签的电影……因此。

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

赋予星球大战，外星人，好人，美人鱼。

但是，如果我搜索具有标签1（4Star）和7（戏剧）的电影，我正在努力和代码挣扎，这将赋予Goodfellows和Godfather。

非常感谢。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

沉睡月亮 2025-02-12 03:18:52

您正在寻找标签1和7都存在的电影。我们通常只想检查是否存在数据时，通常不会使用加入。我们使用存在。或中的，它表达同一件事（在标签1电影集中以及标签7电影集中的电影）中。

这个想法是我们从中选择>我们希望从中看到的表。我们使用其中子句告诉DBMS我们想要看到的行。

我应该补充

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;

。

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;

说，这些并不是获得结果的唯一选择但是它们是直率的。（另一个是有条件的聚合，但您稍后会学到。）

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.)

回复收藏 0 原文

~没有更多了~