"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 =
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
In response to
pgsql-novice by date
|Next:||From: postgresql.org||Date: 2005-05-16 15:27:28|
|Subject: Re: problems with postgresql.msi (installing 8.0.2)|
|Previous:||From: Stephan Szabo||Date: 2005-05-16 13:34:49|
|Subject: Re: concepts?|