SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Two Left Joins? (help with select)

    Okay,

    I have three tables. below are the important columns

    FILMS
    film_id
    views

    VIDEOS
    video_id
    film_id

    PHONE
    phone_id
    video_id

    Now here's what I want to do.

    I want to list the top 15 viewed films (I have a "views" column that I order by), and underneath each film show the videos that have a phone/mobile counterpart. So a single film might have 20 videos in the VIDEOS table with its "film_id". But only two of these 20 have a corresponding row in the PHONE table. The PHONE column "trailer_id" matches up to VIDEOS "video_id"

    so FILMS.film_id=VIDEOS.film_id AND VIDEO.video_id=PHONE.video_id

    Above is the simple select that wouldn't work here, as I want films to be listed even if they don't have a video linked to PHONE

    And some films listed might have none at all, but I still want them on the list.

    For example: the top three films are Star Wars, Terminator and Wanted. Star Wars has three videos that have corresponding PHONE.video_id while Terminator has one, and Wanted none. I want the list to go:

    Star Wars
    -Video 1
    -Video 2
    -Video 3

    Terminator
    -Video 1

    Wanted

    Each video also has a name, which will be echoed.

    So, any suggestions?

    Thanks
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT ...
      FROM films
    INNER
      JOIN videos
        ON videos.film_id = films.film_id
    LEFT OUTER
      JOIN phone
        ON phone.video_id = videos.video_id
    note that the cosmetic re-arrangement of results into the nice indented list you showed is a job for your front-end language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, that was almost it. It listed the top films videos, but it did grab all the videos, even ones that didn't have a related row in PHONE.

    I switched it to this just for sample:

    Code:
    SELECT ...
      FROM films
    INNER
      JOIN videos
        ON videos.film_id = films.film_id
    INNER
      JOIN phone
        ON phone.video_id = videos.video_id
    I switched the left outer to inner, and got all the films listed nice with the trailers that matched and had mobile, but one of the films (that had no mobile) was left out.

    So, with your code, I'm getting all videos of the films listed. So, if a film has 8 videos in VIDEOS, I'm getting them all. Even the ones that don't have PHONE attached to the VIDEO; I don't want that. With my version, the FILMS are being listed and only their PHONE linked VIDEOS are showing, which I want.

    BUT - A couple of FILMS on the list are not being shown, as none of their VIDEO is linked to PHONE. I want the FILMS to show, even if the don't have a VIDEO linked to PHONE.

    Er, this is complicated to say. But we're super close. I need all films to show, all these films have videos, but only some have videos linked to PHONE. For example:

    table FILMS
    films.film_id films.title
    1 ---- Star Wars
    2 ---- Terminator
    3 ---- Wanted

    table VIDEO
    video.video_id video.film_id video.video_title
    1 -- 1 -- Video 1
    2 -- 1 -- Video 2
    3 -- 1 -- Video 3
    4 -- 1 -- Video 4
    5 -- 2 -- Video 1
    6 -- 2 -- Video 2
    7 -- 3 -- Video 1
    8 -- 3 -- Video 2
    9 -- 3 -- Video 3
    10 -- 3 -- Video 4

    table PHONE
    phone.phone_id phone.video_id
    1 -- 1
    2 -- 3
    3 -- 4
    4 -- 5
    5 -- 6

    You'll see that PHONE links to no video_ids for Wanted, so there are none listed there; though it has plenty of videos available not for PHONE.

    So I need these three films to ALL list, with STar Wars and Terminator to show the VIDEO that matches to PHONE. But Wanted should appear too, it just won't have any video to show, as none are on the PHONE table.

    Hope that helps.
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT film_id 
         , films.title
         , videophones.video_id 
         , videophones.video_title 
         , videophones.phone_id 
      FROM films
    LEFT OUTER
      JOIN ( SELECT video.video_id 
                  , video.film_id 
                  , video.video_title
                  , phone.phone_id 
               FROM videos
             INNER
               JOIN phone
                 ON phone.video_id = videos.video_id
           ) AS videophones
        ON videophones.film_id = films.film_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're a god among insects. Wow. That worked nicely.

    Cheers
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.


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
  •