SQL select from a select query

Question

I want to do a select request that perform a first select and then use that selection to perform a second select.

I made a 1st version using a temp table but I would like to know - is there is a way to do it without the temporary table?

My code with the temp table is like:

select  dvd_name, book_name  , count(*) nb
into #t
from usr 
inner join book on usr_book_id  = book_id 
inner join dvd on dvd_id = usr_dvd_id
group by dvd_name, book_name 
having count(*) > 1

select  top 10 usr_smthg,  #t.book_name,dvd_name
from #t
inner join book b on b.book_name = #t.book_name
inner join usr on usr_book_id  = book_id 

    

Answer 1

You can use CTE for that

with t as
(
    select  dvd_name, book_name  , count(*) nb
    from usr 
    inner join book on usr_book_id  = book_id 
    inner join dvd on dvd_id = usr_dvd_id
    group by dvd_name, book_name 
    having count(*) > 1
)

select  top 10 usr_smthg,  t.book_name,dvd_name
from t
inner join book b on b.book_name = t.book_name
inner join usr on usr_book_id  = book_id 

Answer 2

You can use window function with subquery:

select top (10) t.usr_smthg, t.book_name, t.dvd_name
from (select usr_smthg, book_name, dvd_name, 
             count(*) over (partition by dvd_name, book_name) as cnt
      from usr inner join 
           book 
           on usr_book_id  = book_id inner join 
           dvd 
           on dvd_id = usr_dvd_id
     ) t
 where cnt > 1 
 order by ??;

?? indicates ordering column based on you want top (10) records.