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:[EMAIL PROTECTED]]
Sent: Thursday, January 31, 2002 8:27 AM
To: [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to