mysql query join syntax

[andrewlivi]

I want to join the 2 tables so that I can link 2 sets of fields.

Current script:

$query = "SELECT movie_name, people_fullname
FROM movie
LEFT JOIN people
ON movie_leadactor = people_id
ORDER BY movie_name";

What I want to do:

$query = "SELECT movie_name, people_fullname
FROM movie
LEFT JOIN people
ON movie_leadactor = people_id and movie_director = people_id
ORDER BY movie_name";

I'm sure the solution is easy. I just don't know what it is. Thanks for your help.

 

[Olavxxx]

Cant you use a natural join for this?

$query = "SELECT movie_name, people_fullname
    FROM movie, people 
    WHERE movie_leadactor = people_id AND movie_director = people_id
    ORDER BY movie_name";

ps. I might have mis-understood your fieldtypes.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[jpadie]

i nearly always cheat in these circumstances by creating a new access project and linking to the mysql database, then using the query builder to do the first cut of the sql code with the joins etc.

but then I'm lazy.