Re: concepts?

--- "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