Cascading selections in a simple SQL query

Vote count:

0

Let's say we have the following relations in a database:

likes(moviefanname, movietitle) which relates fans to the movies they like and its key is the combination of the 2 attibutes. Suppose moviefanname and movietitle are both strings.

shows(cinema, movietitle, ticketprice) which indicates which movies each cinema shows and at what cost. Its key is the combination of the first 2 attibutes. Suppose cinema is a string and ticketprice is an integer.

I want to find the cinemas which show at least one movie with cost of less than 8 dollars that Fred likes.

The query I created is the following:

select cinema 
from (
    select * 
    from (
        select tmp1.movietitle 
        from likes tmp1, shows tmp2 
        where moviefanname = 'Fred' and tmp1.movietitle = tmp2.movietitle) as foo
    where ticketprice < 8) as foo2) as foo3

First, I joined the 2 tables on their common attribute restricting moviefanname to be "Fred". Then I kept only the tuples with ticket price of 8 or less and finally I did a projection to keep only the names of those cinemas. I tried to execute it in pgadmin SQL editor with no luck since it showed an error that column ticketprice on line 8 does not exist. So, I want to ask if the logic is correct and I would be happy to learn why this error appeared.

asked 58 secs ago

Konstantinos Konstantinidis

133