SQL - Problem to create a view with mulitple joins

Question

I need to create an SQL view that associates the following information for each film:

1) Number of screenings for the film

2) Number of trailer presents in every film screening and their length

3) Number of spot presents in every film screening and their length

I ask sorry for the length of my request, but I tried to be the more clear possible.

I have these tables:

FILM -> id_film Primary Key

id_film    film_title 
1          'Iron Man'
2          'Spider Man'
3          'Thor'

Screening_film -> id_screening PRIMARY KEY, id_film FOREIGN KEY references FILM (id_film)

id_screening   id_film
1               1
2               2
3               3
4               3

SPOT -> id_spot PRIMARY KEY

id_spot       lenght
1               10
2               20
3               30

TRAILER -> id_trailer PRIMARY KEY

id_trailer     lenght
4               10
5               20
6               30

Spot_trailer_screening -> In this table there are the association between spot/trailer and film

id_ST          id_film
1               1
2               2
3               3
4               1
5               2
6               3

This is my code:

CREATE VIEW film_list AS
SELECT f.film_title, 
count(x.id_screening) number_of_screening, count (s.id_spot) num_spot, 
sum(s.lenght) spot_lenght, count(t.id_trailer) num_trailer, 
sum(t.lenght) trailer_lenght
FROM film f
FULL OUTER JOIN screening_film x
ON x.id_film = f.id_film
FULL OUTER JOIN spot_trailer_screening y --The problems begin with this join, the count of number_of_screening isn't correct--
ON y.id_film = f.id_film
FULL OUTER JOIN spot s
ON s.id_spot = y.id_st
FULL OUTER JOIN trailer t
ON t.id_trailer = y.id_st
GROUP BY f.id_film, f.film_title;

Output:

FILM_TITLE      NUMBER_OF_SCREENING NUM_SPOT SPOT_LENGHT  NUM_TRAILER TRAILER_LENGHT
  Iron Man            2                1         10          1            10
  Spider Man          2                1         20          1            20
  Thor                4                2         60          2            60

The correct output should be the following:

FILM_TITLE      NUMBER_OF_SCREENING NUM_SPOT SPOT_LENGHT  NUM_TRAILER TRAILER_LENGHT
  Iron Man            1                1         10          1            10
  Spider Man          1                1         20          1            20
  Thor                2                1         30          1            30

Littlefoot · Accepted Answer

A lot of sample data (lines #1 - 32) makes query pretty long. You, of course, have those tables so your "actual" query begins at line #33.

The next 3 CTEs (lines #34 - 53) (t_screening, t_spot and t_trailer) are used to calculate everything you need and avoid duplicate values you got in your query.

Finally, the last query (begins at line #55) just joins what's already been calculated. If there were films that didn't have trailers or spots, you'd have to use outer join. However, as sample data suggests different, I used inner joins.

As I said: quite long query, so I'll split it into 3 parts to improve readability:

Sample data:

SQL> with
  2  -- sample data
  3  film (id_film, film_title) as
  4    -- I wish I were 12 again ... I'd LOVE these films
  5    (select 1, 'Iron Man'   from dual union all
  6     select 2, 'Spider Man' from dual union all
  7     select 3, 'Thor'       from dual
  8    ),
  9  screening_film (id_screening, id_film) as
 10    (select 1, 1 from dual union all
 11     select 2, 2 from dual union all
 12     select 3, 3 from dual union all
 13     select 4, 3 from dual
 14    ),
 15  spot (id_spot, length) as
 16    (select 1, 10 from dual union all
 17     select 2, 20 from dual union all
 18     select 3, 30 from dual
 19    ),
 20  trailer (id_trailer, length) as
 21    (select 4, 10 from dual union all
 22     select 5, 20 from dual union all
 23     select 6, 30 from dual
 24    ),
 25  spot_trailer_screening (id_st, id_film) as
 26    (select 1, 1 from dual union all
 27     select 2, 2 from dual union all
 28     select 3, 3 from dual union all
 29     select 4, 1 from dual union all
 30     select 5, 2 from dual union all
 31     select 6, 3 from dual
 32    ),

Calculations:

 33  -- calculation
 34  t_screening as
 35    (select s.id_film,
 36            count(*) cnt_screening
 37     from screening_film s join film f on f.id_film = s.id_film
 38     group by s.id_film
 39    ),
 40  t_spot as
 41    (select sts.id_film,
 42            count(*) cnt_spot,
 43            sum(s.length) len_spot
 44     from spot_trailer_screening sts join spot s on sts.id_st = s.id_spot
 45     group by sts.id_film
 46    ),
 47  t_trailer as
 48    (select sts.id_film,
 49            count(*) cnt_trailer,
 50            sum(t.length) len_trailer
 51     from spot_trailer_screening sts join trailer t on sts.id_st = t.id_trailer
 52     group by sts.id_film
 53    )

Final query:

 54  -- final query
 55  select f.id_film,
 56         f.film_title,
 57         c.cnt_screening,
 58         --
 59         s.cnt_spot,
 60         s.len_spot,
 61         --
 62         t.cnt_trailer,
 63         t.len_trailer
 64  from film f join t_screening c on c.id_film = f.id_film
 65              join t_spot s on s.id_film = f.id_film
 66              join t_trailer t on t.id_film = f.id_film
 67  order by f.id_film;

   ID_FILM FILM_TITLE CNT_SCREENING   CNT_SPOT   LEN_SPOT CNT_TRAILER LEN_TRAILER
---------- ---------- ------------- ---------- ---------- ----------- -----------
         1 Iron Man               1          1         10           1          10
         2 Spider Man             1          1         20           1          20
         3 Thor                   2          1         30           1          30

SQL>

SQL - Problem to create a view with mulitple joins

Answers (2)

Related Questions