--- "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
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
> Then if I do this ...
> test=> select DISTINCT
> secteur.description from people, job, secteur where
> job.secteur_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
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
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.
pgsql-novice by date
|Next:||From: Peter Schonefeld||Date: 2005-05-16 15:40:58|
|Subject: COPY from stdin;|
|Previous:||From: postgresql.org||Date: 2005-05-16 15:27:28|
|Subject: Re: problems with postgresql.msi (installing 8.0.2)|