SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Romania
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to write this select?

    Hello,

    I'm having some troubles writing a select. I have the following tables:

    theater
    -------
    - id
    - name

    movie
    -----
    - id
    - name

    showtime
    ---------
    - id
    - theater
    - movie
    - from
    - to
    - schedule

    I would like to list all the theaters with all the available showtimes and movies. I tried to, but i didn't get the result i was expecting. Can somebody help?

    Thanks.

  2. #2
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you want to list theaters, that don't have any movies assigned?

  3. #3
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I hope this will help

    Code MySQL:
    SELECT t.name AS theater, m.name movie, st.`from`, st.`to` FROM theater t 
    INNER JOIN showtime st ON st.theater = t.id 
    INNER JOIN movie m ON m.id = st.movie

    from and to are reserve words, consider using time_from and time_to instead.

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Romania
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, that looks great, but how do I display the theater only once?

  5. #5
    SitePoint Zealot mwasif's Avatar
    Join Date
    Apr 2007
    Location
    Pakistan
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use this at the end of the statement
    GROUP BY t.name

  6. #6
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Psyche View Post
    Ok, that looks great, but how do I display the theater only once?
    Also, note in case you add the GROUP BY clause, you will have to either remove the movie-related fields from SELECT list, or make them aggregated.

    If what you're after is to get result set like this:
    Code:
    Theatre 1,Movie 1
             ,Movie2
    Theatre 3,Movie3
             ,Movie4
             ,Movie5
    then don't do this with SQL.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •