SQL subselect or ...?

[Anon]

Hello!

I have two tables. The first one (called "players[pid, name, surname, address...]") contains player data and the second (called "team[team_name, pid...]") specific players from the first table.

I need an SQL sentence, that lists only the players from the first table, that are not already in the second table (all persons from "players" minus persons that are in "team").

To get this more clear:
I need this to sort players to teams. And since one player can be only in one team, in need to list only those, who arent already in a team.

I have never done something like this and i don't know if something like this can be done in MySQL...

Thanks in advance,
edmund

[[deleted]]

http://www.mysql.com/doc/J/O/JOIN.html

A quote:

"If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:
mysql> select table1.* from table1
LEFT JOIN table2 ON table1.id=table2.id
where table2.id is NULL;

"

[Anon]

I know this may not be very helpful to you because I am not sure about MySql, but with Oracle you can do this:

select pid from players
minus
select pid from teams

The result: all pids that exist in players and do not exist in teams.