[Project] 좌석 페이지 쿼리 작성

select h1.id, h1.started_at, h1.runtime, h1.movie_nm, h1.rating_grade, h1.screen_name, h2.img_uname, h2.img_name, h2.name cinema_name
from
(
select t1.id, t1.started_at, t1.runtime, t1.movie_nm, t1.rating_grade, t2.cinema_id, t2.name screen_name
from
(
select st.id, st.screen_id, st.started_at, m.runtime, m.movie_nm, m.rating_grade from showtime_tb st
inner join movie_tb m on st.movie_id = m.id
where st.id = 1
) t1
inner join screen_tb t2 on t1.screen_id = t2.id
) h1 
inner join cinema_tb h2 on h1.cinema_id = h2.id







select u1.id, u1.col_num, u1.row_num, u1.stime, u1.movie_nm, u1.rating_grade, u1.name screen_name, u2.name cinema_name, u2.img_name, u2.img_uname
from
(
select h1.id, h1.col_num, h1.row_num, h1.showtime_id, h1.stime, h1.movie_nm, h1.rating_grade, h2.name, h2.cinema_id
from
(
select t1.id, t1.col_num, t1.row_num, t1.showtime_id, t1.stime, t2.movie_nm, t2.rating_grade
from
(
select s.id, s.col_num, s.row_num, s.showtime_id, st.movie_id mid, st.screen_id, st.started_at stime from seat_tb s 
inner join showtime_tb st on s.showtime_id = st.id
where s.showtime_id = 1
) t1
inner join movie_tb t2 on t1.mid = t2.id
) h1
inner join screen_tb h2 on h1.showtime_id = h2.id
) u1
inner join cinema_tb u2 on u1.cinema_id = u2.id;









select u1.id, u1.col_num, u1.row_num, u1.stime, u1.movie_nm, u1.runtime, u1.rating_grade, u1.name screen_name, u2.name cinema_name, u2.img_name, u2.img_uname
from
(
select h1.id, h1.col_num, h1.row_num, h1.showtime_id, h1.stime, h1.movie_nm, h1.runtime, h1.rating_grade, h2.name, h2.cinema_id
from
(
select t1.id, t1.col_num, t1.row_num, t1.showtime_id, t1.stime, t2.movie_nm, t2.runtime, t2.rating_grade
from
(
select s.id, s.col_num, s.row_num, s.showtime_id, st.movie_id mid, st.screen_id, st.started_at stime from seat_tb s 
inner join showtime_tb st on s.showtime_id = st.id
where s.showtime_id = 1
) t1
inner join movie_tb t2 on t1.mid = t2.id
) h1
inner join screen_tb h2 on h1.showtime_id = h2.id
) u1
inner join cinema_tb u2 on u1.cinema_id = u2.id;


select *
from
(
select st.id, st.movie_id, st.screen_id, st.started_at, s.col_num, s.row_num from showtime_tb st
inner join seat_tb s on s.showtime_id = st.id
where s.showtime_id = 1
) t1
inner join movie_tb t2 on t1.movie_id = t2.id;



select h1.id, h1.started_at, h1.runtime, h1.movie_nm, h1.rating_grade, h1.screen_name, h2.img_uname, h2.img_name, h2.name cinema_name
from
(
select t1.id, t1.started_at, t1.runtime, t1.movie_nm, t1.rating_grade, t2.cinema_id, t2.name screen_name
from
(
select st.id, st.screen_id, st.started_at, m.runtime, m.movie_nm, m.rating_grade from showtime_tb st
inner join movie_tb m on st.movie_id = m.id
where st.id = 1
) t1
inner join screen_tb t2 on t1.screen_id = t2.id
) h1 
inner join cinema_tb h2 on h1.cinema_id = h2.id;







// 전체 좌석 수 제외하고 필요한 것

select u1.id, u1.col_num, u1.row_num, u1.stime, u1.movie_nm, u1.runtime, u1.rating_grade, u1.name screen_name, u2.name cinema_name, u2.img_name, u2.img_uname
from
(
select h1.id, h1.col_num, h1.row_num, h1.showtime_id, h1.stime, h1.movie_nm, h1.runtime, h1.rating_grade, h2.name, h2.cinema_id
from
(
select t1.id, t1.col_num, t1.row_num, t1.showtime_id, t1.stime, t2.movie_nm, t2.runtime, t2.rating_grade
from
(
select s.id, s.col_num, s.row_num, s.showtime_id, st.movie_id mid, st.screen_id, st.started_at stime from seat_tb s 
inner join showtime_tb st on s.showtime_id = st.id
where s.showtime_id = 1
) t1
inner join movie_tb t2 on t1.mid = t2.id
) h1
inner join screen_tb h2 on h1.showtime_id = h2.id
) u1
inner join cinema_tb u2 on u1.cinema_id = u2.id;




select *
from
(
select st.id, st.movie_id, st.screen_id, st.started_at, s.col_num, s.row_num from showtime_tb st
inner join seat_tb s on s.showtime_id = st.id
where s.showtime_id = 1
) t1
inner join movie_tb t2 on t1.movie_id = t2.id;






// 전체 좌석 수 포함

SELECT u1.id, u1.col_num, u1.row_num, u1.stime, u1.movie_nm, u1.runtime, u1.rating_grade, u1.name  AS screen_name, u2.name AS cinema_name, u2.img_name, u2.img_uname, 

    -- 전체 좌석 수
    (SELECT count(col_num) 
     FROM SEAT_TB 
     WHERE showtime_id = u1.showtime_id) AS total_seat_num,
     
    -- 남은 좌석 수
    ((SELECT count(col_num) 
      FROM SEAT_TB 
      WHERE showtime_id = u1.showtime_id)
     -
     (SELECT count(seat_id) 
      FROM ticket_tb 
      WHERE showtime_id = u1.showtime_id)) AS available_seat_num

FROM
(
    SELECT 
        h1.id, 
        h1.col_num, 
        h1.row_num, 
        h1.showtime_id, 
        h1.stime, 
        h1.movie_nm, 
        h1.runtime, 
        h1.rating_grade, 
        h2.name, 
        h2.cinema_id
    FROM
    (
        SELECT 
            t1.id, 
            t1.col_num, 
            t1.row_num, 
            t1.showtime_id, 
            t1.stime, 
            t2.movie_nm, 
            t2.runtime, 
            t2.rating_grade
        FROM
        (
            SELECT 
                s.id, 
                s.col_num, 
                s.row_num, 
                s.showtime_id, 
                st.movie_id AS mid, 
                st.screen_id, 
                st.started_at AS stime 
            FROM seat_tb s 
            INNER JOIN showtime_tb st 
            ON s.showtime_id = st.id
            WHERE s.showtime_id = 1
        ) t1
        INNER JOIN movie_tb t2 
        ON t1.mid = t2.id
    ) h1
    INNER JOIN screen_tb h2 
    ON h1.showtime_id = h2.id
) u1
INNER JOIN cinema_tb u2 
ON u1.cinema_id = u2.id;