LEFT JOIN on a LEFT JOIN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

LEFT JOIN on a LEFT JOIN

Post by someberry »

I want to get a list of films, and then get each review on them, and then to get a bit more information on that review. If there are no reviews, however, I still want the film to appear - which at the moment they are not.

My current SQL is:

Code: Select all

SELECT      f.film_id,
            r.review_id,
            o.other_id
FROM        `film` f
            LEFT JOIN `review` r
                ON r.film_id   = f.film_id
            LEFT JOIN `other_information` o
                ON o.review_id = r.review_id
WHERE       r.film_id = " . (int)$_GET['film_id] . "
ORDER BY    f.title
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: LEFT JOIN on a LEFT JOIN

Post by onion2k »

You're not selecting the film. You're selecting the review. Change

Code: Select all

WHERE       r.film_id = " . (int)$_GET['film_id] . "
to

Code: Select all

WHERE       f.film_id = " .mysql_real_escape_string($_GET['film_id]). "
The change is replacing the 'r' with 'f'. Escaping the variable is necessary too, but it's nothing to do with fixing the specific problem.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: LEFT JOIN on a LEFT JOIN

Post by josh »

(int) escapes it more the mysql_real_escape_string which parses it as a string and escapes reserved tokens, (int) ensures only an integer is allowed which would rule out everything mysql_real_escape_string would have escaped, plus character data which is probably invalid for that column type anyways. Int will also remove any erroneous whitespace, always use the most restrictive column types and validation when in doubt
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Re: LEFT JOIN on a LEFT JOIN

Post by someberry »

Perfect onion2k! Can't believe I missed that one!

jshpro2, I'm not too sure what you are trying to say. Using (int) would be perfectly acceptable in this situation (not to mention faster due to it being a language construct), and in my opinion preferable since we only want to allow integers, not strings - since the database column is also of type integer. :?:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: LEFT JOIN on a LEFT JOIN

Post by VladSun »

onion2k wrote:You're not selecting the film. You're selecting the review.
That's why I hate when one uses "table as t" ... It's like using a single character for naming vars in PHP code :?

I really can't understand why one should make it short - both bad readability and hard for future maintenance.
I use this form of writing (I use descriptive name for the alias) only when I need a real alias for a table that has been already selected from.

I think one should avoid using "short names" for tables especially when asking for help.

@someberry: It's not personal :
viewtopic.php?f=2&t=88241
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: LEFT JOIN on a LEFT JOIN

Post by josh »

I said the same thing you said :?:
Post Reply