Re: What am I doing wrong?

Tamsin wrote:
>
> 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.
>
> > This is the output I would like:-
> >
> >
> >   player  | club
> > ----------+-------
> >  Rivaldo  | Barca
> >  Kleivert | Barca
> >  Zidane   | Juve
> >  Davids   | Juve
> > (4 rows)
> >

Since the foreign key was declared you can also use the "natural join"
syntax if you are using 7.0.2(?) or later.

Note that you will also need to put the club ids onto the players too:

insert into players values (1, 'Rivaldo', 1);
insert into players values (2, 'Kleivert', 1);
insert into players values (3, 'Zidane', 2);
insert into players values (4, 'Davids', 2);

So:

test=# insert into players values (1, 'Rivaldo', 1);
test=# insert into players values (2, 'Kleivert', 1);
test=# insert into players values (3, 'Zidane', 2);
test=# insert into players values (4, 'Davids', 2);
test=# insert into clubs values (1, 'Barca');
test=# insert into clubs values (2, 'Juve');
test=# SELECT player, club from players natural join clubs;
  player  | club
----------+-------
 Rivaldo  | Barca
 Kleivert | Barca
 Zidane   | Juve
 Davids   | Juve
(4 rows)

test=#

Cheers,
                    Andrew.
--
_____________________________________________________________________
           Andrew McMillan, e-mail: Andrew@catalyst.net.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267