I have three tables. below are the important columns
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:
Each video also has a name, which will be echoed.
So, any suggestions?