On Thu, Jan 18, 2001 at 12:29:42AM +1300, Andrew McMillan wrote:
> Tamsin wrote:
> >
> > try this
> >
> > select player,club from players,clubs where players.club_id = clubs.club_id
> 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:
Yes, I noticed as soon as I posted this that I had completely ommitted
the player's club_ids...
> 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;
This works fine and it's simpler.
BTW how do I limit this select to players from 'Juve' ?
I tried appending "where club = 'Juve'", but this returned 0 rows...
> 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
>
--
John