Re: concepts?

"D.C." <coughlandesmond(at)yahoo(dot)fr> writes:
> 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 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 ?

The conceptual model of SQL is that "FROM t1, t2" generates the
Cartesian product (cross product) of the two tables --- that is,
you get a join row for every possible combination of rows from
the inputs. Then the WHERE clause selects out just the rows
you want from the join table.

Of course, a great deal of work goes into making the actual
implementation more efficient than that ;-). But that's the
theoretical basis. If you don't write any WHERE then you
get the whole join table.

> test=> select DISTINCT people.nom,people.prenom,job.boite,
> secteur.description from people, job, secteur where job.secteur_id =
> secteur.sector_id;

Same problem here: you have an underconstrained join to "people".
Not knowing anything about your data model, I'm not sure if
people.id = job.id is the thing to add or not.

regards, tom lane