nested select or join query?

Question

i am new to sql so this may have a very basic answer but the question to answer is as follows .....

which film has taken the least takings at a performance? include film name and cinema name in the result.?

film name and cinema name are in two different table film and cinema. takings are in the performance table. i cant figure out how to complete this query. this is what i have got so far but it comes with an error in line 3 column 7.

select cinema_no,film_no
from CINEMA, film
where takings ( select min(takings)
from performance);

Answer 1

Because of multiple tags with oracle, I ignored the tag mysql ( of one which you should get rid of. e.g. please decide which DBMS are you using, by the way I already removed the irrelevant one oracle-sqldeveloper ).

It seems you need such a select statement ( prefer using modern ANSI-92 JOIN syntax, easily maintained and understandable ) with ordering by descending sum and contribution of row_number function as :

SELECT Name, Sum_Takings 
  FROM
  (
   SELECT f.Name, sum(p.Takings) Sum_Takings,
          row_number() over (ORDER BY sum(p.Takings)) as rn
     FROM Film f 
     LEFT JOIN Cinema c ON f.Cinema_ID = c.ID
     LEFT JOIN Performance p ON f.ID = p.id_film
    GROUP BY f.Name
   )
  WHERE rn = 1;

with added DDL statement as in the following :

SQL>  CREATE TABLE Cinema (
  2     ID     integer PRIMARY KEY NOT NULL,
  3     Title  varchar2(100) NOT NULL
  4   );

Table created

SQL>  CREATE TABLE Film (
  2     ID   integer PRIMARY KEY NOT NULL,
  3     Name varchar2(100) NOT NULL,
  4     Cinema_ID integer
  5       CONSTRAINT fk_Cinema_ID REFERENCES Cinema(ID)
  6   );

Table created

SQL>   CREATE TABLE Performance (
  2     ID      integer PRIMARY KEY NOT NULL,
  3     ID_Film integer
  4          CONSTRAINT fk_Film_ID REFERENCES Film(ID),
  5     Takings integer
  6   );

Table created

SQL>  INSERT ALL
  2         INTO Cinema(ID,Title) VALUES(1,'NiteHawk')
  3         INTO Cinema(ID,Title) VALUES(2,'Symphony Space')
  4         INTO Cinema(ID,Title) VALUES(3,'The Ziegfeld')
  5         INTO Cinema(ID,Title) VALUES(4,'Cinema Village')
  6       SELECT * FROM dual;

4 rows inserted

SQL>  INSERT ALL
  2         INTO Film(ID,Name,Cinema_ID) VALUES(1,'Citizen Kane',1)
  3         INTO Film(ID,Name,Cinema_ID) VALUES(2,'Titanic',2)
  4         INTO Film(ID,Name,Cinema_ID) VALUES(3,'Brave Heart',4)
  5         INTO Film(ID,Name,Cinema_ID) VALUES(4,'Dumb and Dummer',3)
  6         INTO Film(ID,Name,Cinema_ID) VALUES(5,'How To Train Your Dragon',2)
  7         INTO Film(ID,Name,Cinema_ID) VALUES(6,'Beetle Juice',3)
  8       SELECT * FROM dual;

6 rows inserted

SQL>  INSERT ALL
  2         INTO Performance VALUES(1,1,15)
  3         INTO Performance VALUES(2,1,4)
  4         INTO Performance VALUES(3,2,10)
  5         INTO Performance VALUES(4,3,1)
  6         INTO Performance VALUES(5,4,5)
  7         INTO Performance VALUES(6,3,3)
  8         INTO Performance VALUES(7,2,7)
  9         INTO Performance VALUES(8,5,7)
 10         INTO Performance VALUES(9,6,6)
 11       SELECT * FROM dual;

9 rows inserted

SQL> commit;

Commit complete

SQL> SELECT Name, Sum_Takings
  2    FROM
  3    (
  4     SELECT f.Name, sum(p.Takings) Sum_Takings,
  5            row_number() over (ORDER BY sum(p.Takings)) as rn
  6       FROM Film f
  7       LEFT JOIN Cinema c ON f.Cinema_ID = c.ID
  8       LEFT JOIN Performance p ON f.ID = p.id_film
  9      GROUP BY f.Name
 10     )
 11    WHERE rn = 1
 12  ;

NAME                                                                  SUM_TAKINGS
--------------------------------------------------------------------- -----------
Brave Heart                                                                     4

dbfiddle.uk demo

Answer 2

this will work:

select * from (select c.cinema_name,f.film_name
from cinema c, film f,performance p,
rank() over (partition by c.cinema order by sum(p.takings)) as rank
where
c.ID=f.cinema_id and
f,id=p.id
group by f.film_name) where rank=1
;

Answer 3

As it involves grouping of Performance table you can achieve it by using two queries one to get the cinemaID and filmID of minimum performance takings and the another one to get the cinema name and filmname using the collected ids

SELECT CinemaID,FilmID from Performance where PerformanceTaking=(Select Min(PerformanceTaking) from Performance);

SELECT CinemaName,FilmName from Cinema,Film where CinemaID=1 and FilmID=1;