Skip site navigation (1) Skip section navigation (2)

Re: What am I doing wrong?

From: Andrew McMillan <Andrew(at)catalyst(dot)net(dot)nz>
To: Tamsin <tg_mail(at)bryncadfan(dot)co(dot)uk>
Cc: John Poltorak <jp(at)eyup(dot)org>, pgsql-novice(at)postgresql(dot)org
Subject: Re: What am I doing wrong?
Date: 2001-01-17 11:29:42
Message-ID: 3A658226.BD973779@catalyst.net.nz (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)catalyst(dot)net(dot)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


In response to

Responses

pgsql-novice by date

Next:From: John PoltorakDate: 2001-01-17 12:08:35
Subject: Re: What am I doing wrong?
Previous:From: Orlando Eloy GentilDate: 2001-01-17 11:04:11
Subject: Problems with authentication

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group