mysql query join statement

Question

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/

@RocketHazmat it is working but i need more data from more tables...i did a serie of joining table from member -->movie...but i still need to use theater, cinema and seat table — ouzoumzak
– ouzoumzak, Commented Apr 15, 2013 at 19:20
i still don't understand the question? join theater to show like your ERD shows. — Matt Busche
– Matt Busche, Commented Apr 15, 2013 at 19:21

Mahmoud Gamal · Accepted Answer · 2013-04-15 19:22:02Z

1

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

answered Apr 15, 2013 at 19:22

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt Busche · Accepted Answer · 2013-04-15 19:24:03Z

1

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

answered Apr 15, 2013 at 19:24

Matt Busche

14.4k5 gold badges39 silver badges63 bronze badges

Collectives™ on Stack Overflow

mysql query join statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related