Re: concepts?

On Mon, 16 May 2005, D.C. 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) ?

In the abstract "from table1, table2" means precisely join each row in
table1 with each row in table2 and output all the generated rows. The
system doesn't implicitly know which rows in people match to which rows in
job so you have to tell it how.

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

You can use a where clause to act as a condition to filter the full joined
set down to only those rows that match. In practice of course we generally
do not do the join and then filter, but the results should match that
behavior.

Is people.id really meant to hold the id of their job? That seems rather
confusing. If that's not the job id, you need some way to say which person
has which job. If it's a relationship such that each person can only have
one job, you can store the jobid inside people, otherwise you may want a
separate table to hold which people belong to which jobs.

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

You'd need to constrain people and job as well.