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

Re: concepts?

From: <operationsengineer1(at)yahoo(dot)com>
To: "D(dot)C(dot)" <coughlandesmond(at)yahoo(dot)fr>, pgsql-novice(at)postgresql(dot)org
Subject: Re: concepts?
Date: 2005-05-16 15:38:03
Message-ID: 20050516153803.6567.qmail@web52401.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
--- "D.C." <coughlandesmond(at)yahoo(dot)fr> wrote:
> X-No-Archive: true
> 
> Hi again,
> My test database is taking shape, and I'm starting
> to get the hang of 
> it, but there's one concept that I just can't get my
> head around, and 
> it is this: if I do ..
> 
> test=> select *  from people, job;
> 
> .. why does every entry get displayed seven times
> (there are seven 
> 'people' in each table) ?

i was reading up on this b/c i saw similar behavior
last week.

i believe it is what is called a cartesian join.  it
takes the each table one row set and matches it with
each each table two row set.
 
> In other words, why do I *need* to do this ..
> 
> test=> select * from people, job where people.id =
> job.id ;
> ... in order for every name to be displayed just
> once ?

since the cartesian join will combine every row in
table 1 to every row in table 2 (by definition), you
need to eliminate some of the data by putting in this
constraint.

> Then if I do this ...
> 
> test=> select DISTINCT
> people.nom,people.prenom,job.boite, 
> secteur.description from people, job, secteur where
> job.secteur_id = 
> secteur.sector_id;
> 
> ... every single person gets printed seven times,
> with their belonging 
> to each sector of activity.  :-(   So I get 42 rows,
> whereas I only 
> want seven: one for each person, with their name,
> their first name, 
> their company name, and the 'sector' in which that
> company fits.  For 
> example, my wife is a teacher, and so she appears as
> 'SMITH JOAN 
> TEACHING CIVIL_SERVICE.  Perfect.  Except that she
> also gets listed as 
> working for the *other* six companies in the db, and
> as belonging to 
> their respective sectors.
> 
> What am I doing wrong ?  :-(

sounds like you still have a cartesian join.  you need
to apply another constraint.  from the looks of it,
you need to constrain the company, too.

test=> select DISTINCT
people.nom,people.prenom,job.boite, 
secteur.description from people, job, secteur where
job.secteur_id = secteur.sector_id;
AND [fill in constraint for the company value].

i think the above will work, although, i am new to
this, too.

you may be able to learn some more by googling "sql
three table joins".



		
__________________________________ 
Yahoo! Mail Mobile 
Take Yahoo! Mail with you! Check email on your mobile phone. 
http://mobile.yahoo.com/learn/mail 

Responses

pgsql-novice by date

Next:From: Peter SchonefeldDate: 2005-05-16 15:40:58
Subject: COPY from stdin;
Previous:From: postgresql.orgDate: 2005-05-16 15:27:28
Subject: Re: problems with postgresql.msi (installing 8.0.2)

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