RE: [PHP-DB] left joins and mysql

First, don't try to force JOIN to do something that is is not designed to do. Use WHERE to restrict your query. Try: SELECT t.team_name,t.flight_id,p.player_fname,p.player_lname FROM team t LEFT JOIN player p ON t.team_id=p.player_team_id WHERE t.flight_id=p.player_flight_id && p.player_status='captain' ORDER by t.flight_id -----Original Message----- From: tom hilton [mailto:tom-hilton@home.com] Sent: Thursday, January 31, 2002 8:27 AM To: php-db@lists.php.net Subject: [PHP-DB] left joins and mysql Hi, I am trying to do a left join on a "master" table, but I don't want to pull all the records from this table. Can anyone tell me the syntax for using a WHERE or equivalent on the master table before left joining the slave table? Is this possible, or do I have to pull all the records from the master? I have a table called "team" that is the master, I want to join a table called "player" on it, but I only want teams that belong to one particular league (flight_id), not all teams (there are eight flights in the "team" table). Below is the basic syntax I am using now, and it is pulling all the teams from the team table, not just the teams where flight_id=1. Is there someplace after the "join" that I can restrict "team" records using a WHERE statement? Sorry if this is confusing. $big_join=mysql_query SELECT team.team_name,team.flight_id,player.player_fname,player.player_lname FROM team left join player on team.flight_id=1 and team.team_id=player.player_team_id and team.flight_id=player.player_flight_id and player.player_status='captain' ORDER by team.flight_id or die("no friggin way") -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net