CODEWITHSUNDEEP
mysqlsqldatabaseselectview
user3104367
user3104367

Reputation: 11

SQL query from a View

So i have two database tables called "video" and "related". The table "video" has 3 columns: id, views and category. The table "related" has 2 columns: a (same as the column id of the table "video") and b (id of the related video to a).

Here is an example of table "video":

ID | views | category

1 | 11000 | music

2 | 13000 | art

3 | 14000 | music

4 | 60000 | music

5 | 80000 | art

......

An example of table "related"

a | b

1 | 2

1 | 3

2 | 1

2 | 4

2 | 5

......

As you can see the id of the related video can be found on the table "video" as well.

So now I have this View:

And I need to convert it into an SQL query. I need to state the following: from all the video IDs with views above 10000 which ones of their related videos have category = "music".

I hope you don't find it as confusing as it seems...Thank you for your time and i am waiting for any possible help...

Upvotes: 0

Views: 58

Answers (1)

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79838

select * 
from video v1 
where v1.id in (
   select r.a 
   from related r 
   join video v2 on r.b = v2.id 
   where v2.category = 'music' )
and v1.views >= 10000

Edit

In your comment below, you asked for something completely different from the original question - namely, you NOW want videos in category "music" where the related video has more than 10000 views. The SQL for your NEW question is this.

select * 
from video v1 
where v1.id in (
   select r.a 
   from related r 
   join video v2 on r.b = v2.id 
   where v2.views > 10000 )
and v1.category = 'music'

but please, next time you want to ask a different question from the original, just make a new question.

Upvotes: 1

Related Questions