try this
select player,club from players,clubs where players.club_id = clubs.club_id
You need the where clause in the query to join the tables, otherwise you get
a 'cartesian product' where all combinations of rows are displayed. The
foreign key constraint just constrains the data in the tables, so that you
can't enter a club_id into players which isn't in clubs, but doesn't affect
what you can select from the tables.
HTH
Tamsin
>
>
> select player,club from players,clubs;
> ------------------------------------------
>
> This is the output I get:-
>
>
> player | club
> ----------+-------
> Rivaldo | Barca
> Kleivert | Barca
> Zidane | Barca
> Davids | Barca
> Rivaldo | Juve
> Kleivert | Juve
> Zidane | Juve
> Davids | Juve
> (8 rows)
>
> This is the output I would like:-
>
>
> player | club
> ----------+-------
> Rivaldo | Barca
> Kleivert | Barca
> Zidane | Juve
> Davids | Juve
> (4 rows)
>