Need Two Left Joins? (help with select)

[casbboy]

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

[r937]

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

[casbboy]

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:

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

[r937]

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

[casbboy]

You're a god among insects. Wow. That worked nicely.

Cheers
Ryan