Обсуждение: What am I doing wrong?
I think I'm doing something fundamenatally wrong when attempting
to use multiple tables. Can someone point out what I'm missing
from the following snippet:-
------------------------------------------
CREATE TABLE players
(player_id INTEGER NOT NULL Primary key,
player VARCHAR,
club_id INTEGER);
CREATE TABLE clubs
( club_id INTEGER NOT NULL Primary key,
club VARCHAR ,
foreign key (club_id) references players);
insert into players values (1, 'Rivaldo');
insert into players values (2, 'Kleivert');
insert into players values (3, 'Zidane');
insert into players values (4, 'Davids');
insert into clubs values (1, 'Barca');
insert into clubs values (2, 'Juve');
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)
It's probably obvious to everyone, but I don't see what I've done
wrong. Any enlightenment would be much appreiated.
--
John
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) >
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
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
On Wed, Jan 17, 2001 at 12:08:35PM +0000, John Poltorak wrote: > On Thu, Jan 18, 2001 at 12:29:42AM +1300, Andrew McMillan wrote: > > 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... Oops! It does work, when you type it correctly :-). -- John