CODEWITHSUNDEEP
mysql
ouzoumzak
ouzoumzak

Reputation: 95

mysql query join statement

I have a problem in a query. i need to make a page where user can see all reservations he made with movie name, cinema name, seats code that he reserved

i reached this level

SELECT member.member_username, show_datetime, movie_name
FROM `member`
JOIN `reservation` ON `reservation`.`member_id` = `member`.`member_id`
JOIN `show` ON `show`.`show_id`= `reservation`.`show_id`
JOIN `movie` ON `movie`.`movie_id` = `show`.`movie_id`
WHERE `reservation`.`member_id`=1

i need to make a connection and get also the cinema_name from cinema table and seats_code from seat table and theater name in fact, i need a query to give me all almost all data in my whole database

here is the schema for the DB http://imageshack.us/photo/my-images/824/58054944.jpg/

Upvotes: 0

Views: 56

Answers (2)

Matt Busche
Matt Busche

Reputation: 14333

Keep joining your tables

SELECT member.member_username, show_datetime, movie_name, c.cinema_name, t.theater_name
FROM `member`
    JOIN `reservation` ON `reservation`.`member_id` = `member`.`member_id`
    JOIN `show` ON `show`.`show_id`= `reservation`.`show_id`
    JOIN `movie` ON `movie`.`movie_id` = `show`.`movie_id`
    JOIN `theater` ON `show`.`theater_id` = `theater`.`theater_id`
    JOIN `cinema` ON `theater`.`cinema_id` = `cinema`.`cinema_id`
    JOIN `seat` ON `show`.`theater_id` = `seat`.`theater_id`
WHERE `reservation`.`member_id`=1

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

JOIN these two tables too:

SELECT 
  m.member_username, 
  sh.show_datetime, 
  v.movie_name,
  c.cinema_name,
  t.theater_name
FROM member            AS m
INNER JOIN reservation AS r  ON r.member_id  = m.member_id
INNEr JOIN show        AS sh ON sh.show_id   = r.show_id
INNER JOIN movie       AS v  ON v.movie_id   = s.movie_id
INNER JOIN theater     AS t  ON t.theater_id = sh.theater_id
INNER JOIN cinema      AS c  ON c.theater_id = sh.theater_id 
WHERE r.member_id = 1

Upvotes: 1

Related Questions