Re: difficult join?

[Benjamin Pflugmann <benjamin-mysql@xxxxxxxxxxxx>]

Hi.

On Mon, Dec 03, 2001 at 09:30:41PM -0800, jaskirat_singh@xxxxxxxxx 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.
[...]


-- 
benjamin-mysql@xxxxxxxxxxxx

---------------------------------------------------------------------
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 <mysql-thread92947@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php