How to select from table where value from another table is greather than some value?-CodePudding

How could I select * from table movies where seeds > 10 in table torrents? Table movies has unique id, while table torrents has id that matches the movie id. There are few torrents for each movie and having just one that has seeds greather than 10 should be selected.

The only sql I have is this where it deletes all movies which don't have any torrents.

DELETE FROM movies WHERE NOT EXISTS ( SELECT id FROM torrents WHERE id=movies.id)

I'm not good at mysql at that level to make it, even with this example, which I also got from around here.

Help would be very much appreciated.

CodePudding user response：

something like the following:

select *
from movies m
where exists (
  select * from torrents t
  where t.id = m.id and t.seeds > 10
);

CodePudding user response：

id from torrents where seeds > 10

select * from movies where movie_id in (select id from torrents where seeds > 10);

you can also write it as

select a.id from movies a join torrents b on a.movie_id = b.id
where b.seeds > 10;

CodePudding user response：

you need to join the second table

 SELECT *  FROM movies as m INNER JOIN torrents as t ON m.id = t.movie
 WHERE t.seeds > 10