CODEWITHSUNDEEP
mysqlsqlmysql-workbench
agent18
agent18

Reputation: 2297

from subquery reuse in where statement

My problem:

I want to reuse the table in the from subquery in the where clause. How do I do that?

select rID, name, title
from (select statement ...) as t1 
where exists(select * from t1 as t2
            where t1.ratingDate>t2.ratingDate and t1.stars>t2.stars);

This gives an error: Error Code: 1146. Table 'sql_stanford_movie.t1' doesn't exist

Goal: Return rID such that

Main table:

enter image description here

Part 1 of solution: After groupby rID and mID and count==2:

enter image description here

I make a temp table with following:

select *
from Rating R1 join 
    (select rID,mID, count(mID)
    from Rating
    group by rID, mID
    having count(mID)=2) as G using(rID,mID);

Part 2 of solution: Check if Latest ratingDAte has higher stars

enter image description here

select rID, name, title
from temp as t1 
where exists(select * from temp t2
            where t1.ratingDate>t2.ratingDate and t1.stars>t2.stars);

But I need to write the whole thing in one statement.

Upvotes: 0

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You can do this with a CTE:

with t1 as (
      . . . 
     )
select rID, name, title
from t1 
where exists (select 1
              from t1 t2
              where t1.ratingDate > t2.ratingDate and 
                    t1.stars > t2.stars
             );

A CTE is like a subquery, except you can refer to it multiple times.

That said, I'm guessing that you can use window functions for this. But the logic you want isn't exactly clear.

Upvotes: 2

Related Questions