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 if 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

Andrey Morozov · Accepted Answer · 2019-03-06T09:05:34.433

8

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

edited Mar 06 '19 at 09:05

answered Feb 26 '19 at 15:56

Andrey Morozov

7,839
5
53
75

alright, I was quite close to that but didn't know the wording "with t as". Thanks a lot for your quick answer, I mark it as accepted answer as it it very close to my code. Thank you. PS : I can't edit since it's less than 6 char but in your code it's not #t anymore but t – hunB Feb 26 '19 at 16:07
@hunB -- `#t` means something specific on SQL Server -- it is called a temporary table -- this example (or mine) can't use `#t` because of that. – Hogan Feb 26 '19 at 16:35
1

semi colon before the with t as well https://stackoverflow.com/questions/6938060/common-table-expression-why-semicolon – SteveB Mar 06 '19 at 09:18
You can create views too and then use `SELECT` statements on it, to avoid the query from getting complicated. – Suraj Feb 19 '21 at 07:12

score 2 · Answer 2 · answered Feb 26 '19 at 15:54

In sql you can use a sub-query, like this:

select  top 10 usr.usr_smthg,  t.book_name, usr.dvd_name
from (
  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
) t
inner join book b on b.book_name = t.book_name
inner join usr on usr_book_id  = book_id 
-- guess
order by n.nb desc

Yogesh Sharma · Answer 3 · 2019-02-26T16:04:35.123

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.

score 0 · Answer 4 · answered Apr 23 '23 at 10:28

Usually, there are better ways to get the proper result, than selecting from a SELECT result (such as JOIN, GROUP BY, and subselects). However, when using a window function such as ROW_NUMBER() it may become necessary to select from a result.

This can be accomplished by WITH ... AS.

WITH result
AS (SELECT ... FROM ...)
SELECT *
FROM result
WHERE ...

For details see https://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/

SQL select from a select query

4 Answers4