null foreign keys

midget · #1 (**permalink**) 01-29-04, 15:14

HI everybody...
I have the following tables:

Code:

CREATE TABLE player(
   id_player varchar(10),
   first_name varchar(20),
   last_name varchar(50),
   id_team varchar(10));

CREATE TABLE team(
   id_team varchar(10),
   team_name varchar(50));

I have the following situation:
* id_team is a foreign key in the "player" table.
* There are players that doesn't have a team.

if I make the following select:

Code:

  SELECT P.first_name,P.last_name,T.team_name FROM
                 player P, team T WHERE
                           P.id_team=T.id_team

and the player does not have a valid team, I don't receive any recor in return despite the player exists.
Is there a way to retreive the record despite my foreign key(id_team) is null?
thanks in advance for any help.
regards,
-eduardo s.m.

midget · #2 (**permalink**) 01-29-04, 16:44

I found the solution myself:
this resolves the problem:

Code:

SELECT   P.first_name,P.last_name,T.team_name FROM
                player P left outer join team T on
                P.id_team=T.id_team WHERE
                id_player=1

greetings,
-eduardo s.m.

Internet Services

Web Development

Digital Marketing

Consumer Tech