Hi.
On Mon, Dec 03, 2001 at 09:30:41PM -0800, [EMAIL PROTECTED] wrote:
> I have the following situation
>
> Three tables
>
> 1) players
> 2) teams
> 3) team_player
>
> 'players' holds player records and player_id is auto_increment primary key
> 'teams' holds team records and team_id is auto_increment primary key
> 'team_player' is a table which holds records for
> "which player belongs to which team"
>
> That is team_player has 3 columns
> 1) id (auto_increment primary key)
> 2) team_id
> 3) player_id
>
>
> My problem is that I want to extract the record set from 'players' table
> for those players which do not belong to any team
>
> ie they will not have player_id in 'team_player' table.
This should work:
SELECT p.*
FROM players p
LEFT JOIN team_player tp ON tp.player=p.id
WHERE tp.player IS NULL
This is even documented in the fine manual:
http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
Bye,
Benjamin.
> I don't know how to write the join in the query .. if it is possible.
>
> Any help is greatly appreciated.
[...]
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
