Обсуждение: concepts?
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 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 ? 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 ? :-( D.
--- "D.C." <coughlandesmond@yahoo.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
Hi guys, Look in postgreSQL documentation about the JOINing of tables, NATURAL JOIN, INNER JOINs, OUTER JOINs (LEFT & OUTER) and you will learn how to do this you want to. Follow this link : http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html Theres's somethign about ....;-) Hope this help, Andrew __________________________________ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
"D.C." <coughlandesmond@yahoo.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
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.
Hello all. I am having a problem on using pg 8.0.2 with apache 2.0.43. While sending queries and obtaining results was absolutely no problem when doing stuff outside an apache module, as soon as I moved the code into apache, I started being short on replies from PG. Searching and googling the whole day didn't do any good so I am wondering whether any of you does have any similar experience and does know some workarounds. The error postgres is throwing when setting debug levels to 5 is something of a mouthful (I am on a different computer than the one I did the work on so I am writing this from memory) and describes an out-of-resources error or a full queue, a socks related error. In any case, nothing close to reality (plenty of resources available). The connection to the PG is done and the server receives the connection request. Soon after though, everything goes quiet. Any ideas, advices on this? I hope I am hot _way_ too fuzzy. I am hoping that someone actually did have this problem before or have been around it. Thank you and best regards, Razvan
Hello, From the information that you've provided, it sounds like the problem may quite possibly be with your socks. I used to have problems with my socks all the time too. I've switched to a new laundry detergent, and everything is fine and dandy again. Tim PS. If you post the actual error message that you're receiving, you'll probably get more helpful responses. On Mon, May 16, 2005 at 11:23:07AM -0700, cbrazvan@laitek.com wrote: > Hello all. > > I am having a problem on using pg 8.0.2 with apache 2.0.43. While sending > queries and obtaining results was absolutely no problem when doing stuff > outside an apache module, as soon as I moved the code into apache, > I started being short on replies from PG. > > Searching and googling the whole day didn't do any good so I am wondering > whether any of you does have any similar experience and does know some > workarounds. > > The error postgres is throwing when setting debug levels to 5 is something > of a mouthful (I am on a different computer than the one I did the work on > so I am writing this from memory) and describes an out-of-resources > error or a full queue, a socks related error. In any case, nothing close to > reality (plenty of resources available). > > The connection to the PG is done and the server receives the connection > request. Soon after though, everything goes quiet. > > Any ideas, advices on this? > > I hope I am hot _way_ too fuzzy. I am hoping that someone actually did > have this problem before or have been around it. > > Thank you and best regards, > Razvan > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Вложения
Yuck-Yuck. As I was telling you, I did not have access to my development machine at the time I wrote the post and the error is weird enough that log messages are not much of a help (or so I think). I was aiming for someone who had a similar experience. Anyway. I got to the machine and here are the error messages I was talking about. 2005-05-16 12:48:17 LOG: XX000: could not receive data from client: An operation on a socket could not be performed becausethe system lacked sufficient buffer space or because a queue was full. 2005-05-16 12:48:17 LOCATION: pq_recvbuf, pqcomm.c:704 2005-05-16 12:48:17 LOG: 08P01: unexpected EOF on client connection Thanks. Razvan P.S. Which IS your new laundry detergent? >Hello, > > From the information that you've provided, it sounds like the problem >may quite possibly be with your socks. I used to have problems with my >socks all the time too. I've switched to a new laundry detergent, and >everything is fine and dandy again. > >Tim > >PS. If you post the actual error message that you're receiving, you'll >probably get more helpful responses.
Sorry to cut in here guys but how long does it take to get subscribed to the list? I have subscribed to the list and I am receiving email from the list but my questions are going to never-neverland with MJ and I am not seeing them here. I have a real problem here and I think I have wasted a whole day waiting. Again sorry to interrupt! Buddy -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Tim Goodaire Sent: Monday, May 16, 2005 2:55 PM To: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Problem with Out-of-resources error? Hello, From the information that you've provided, it sounds like the problem may quite possibly be with your socks. I used to have problems with my socks all the time too. I've switched to a new laundry detergent, and everything is fine and dandy again. Tim PS. If you post the actual error message that you're receiving, you'll probably get more helpful responses. On Mon, May 16, 2005 at 11:23:07AM -0700, cbrazvan@laitek.com wrote: > Hello all. > > I am having a problem on using pg 8.0.2 with apache 2.0.43. While > sending queries and obtaining results was absolutely no problem when > doing stuff outside an apache module, as soon as I moved the code into > apache, I started being short on replies from PG. > > Searching and googling the whole day didn't do any good so I am > wondering whether any of you does have any similar experience and does > know some workarounds. > > The error postgres is throwing when setting debug levels to 5 is > something of a mouthful (I am on a different computer than the one I > did the work on so I am writing this from memory) and describes an > out-of-resources error or a full queue, a socks related error. In any > case, nothing close to reality (plenty of resources available). > > The connection to the PG is done and the server receives the > connection request. Soon after though, everything goes quiet. > > Any ideas, advices on this? > > I hope I am hot _way_ too fuzzy. I am hoping that someone actually did > have this problem before or have been around it. > > Thank you and best regards, > Razvan > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) >
Unfortunately, I've never seen that message before so I can't help. Chances are that someone else will know what it means though. What operating system are you using? Is it Windows? Tim On Mon, May 16, 2005 at 10:09:30PM +0300, Razvan Costea-Barlutiu wrote: > Yuck-Yuck. > > As I was telling you, I did not have access to my development machine at the time I wrote the post and > the error is weird enough that log messages are not much of a help (or so I think). > I was aiming for someone who had a similar experience. > > Anyway. I got to the machine and here are the error messages I was talking about. > > 2005-05-16 12:48:17 LOG: XX000: could not receive data from client: An operation on a socket could not be performed becausethe > system lacked sufficient buffer space or because a queue was full. > 2005-05-16 12:48:17 LOCATION: pq_recvbuf, pqcomm.c:704 > 2005-05-16 12:48:17 LOG: 08P01: unexpected EOF on client connection > > Thanks. > Razvan > P.S. Which IS your new laundry detergent? > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Вложения
Le 16 mai 05, à 21:20, Buddy Shearer a écrit : > Sorry to cut in here guys but how long does it take to get subscribed > to the > list? I have subscribed to the list and I am receiving email from the > list > but my questions are going to never-neverland with MJ and I am not > seeing > them here. I have a real problem here and I think I have wasted a > whole day > waiting. That one was received all right ... :-) D. -- Des Coughlan coughlandesmond@yahoo.fr
Yes, it is windows XP, SP2. I also removed the AV I was using with no results and there is no firewall running on the machine. Any more ideas, advices? Thank you, Razvan >Unfortunately, I've never seen that message before so I can't help. >Chances are that someone else will know what it means though. > >What operating system are you using? Is it Windows? > >Tim > >On Mon, May 16, 2005 at 10:09:30PM +0300, Razvan Costea-Barlutiu wrote: >> Yuck-Yuck. >> >> As I was telling you, I did not have access to my development machine at the time I wrote the post and >> the error is weird enough that log messages are not much of a help (or so I think). >> I was aiming for someone who had a similar experience. >> >> Anyway. I got to the machine and here are the error messages I was talking about. >> >> 2005-05-16 12:48:17 LOG: XX000: could not receive data from client: An operation on a socket could not be performed becausethe >> system lacked sufficient buffer space or because a queue was full. >> 2005-05-16 12:48:17 LOCATION: pq_recvbuf, pqcomm.c:704 >> 2005-05-16 12:48:17 LOG: 08P01: unexpected EOF on client connection >> >> Thanks. >> Razvan >> P.S. Which IS your new laundry detergent? >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >>
Le 16 mai 05, à 17:38, <operationsengineer1@yahoo.com> a écrit :
>> 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.
OK, now this is one concept I need explaining: do I need to set up
'links' from one table to another when I create the tables? Here is
the 'clients' table of a new db that I've created ...
\d clients
Table "public.clients"
Column | Type | Modifiers
------------------+-----------------------+-----------
client_id | integer | not null
nom | character varying(25) | not null
prenom | character varying(25) | not null
dob | date |
courriel | character varying(25) | not null
numero_rue | numeric | not null
nom_rue | character varying(50) | not null
cp | character varying(6) | not null
ville | character varying(25) | not null
pays | character varying(10) | not null
telephone | character varying(15) | not null
date_inscription | date |
dernier_achat | date |
Indexes:
"clients_pkey" PRIMARY KEY, btree (client_id)
Check constraints:
"clients_check" CHECK (dernier_achat > date_inscription)
As you can see, there is a primary key which is the client_id. Now the
'purchases' table..
\d ventes
Table "public.ventes"
Column | Type | Modifiers
--------------+---------+-----------
vente_number | integer | not null
titre_id | integer | not null
prix_vente | numeric | not null
vendu_a | integer | not null
vendu_le | date | not null
Indexes:
"ventes_pkey" PRIMARY KEY, btree (vente_number)
Check constraints:
"ventes_vendu_le_check" CHECK (vendu_le > '2005-12-31'::date)
Foreign-key constraints:
"ventes_titre_id_fkey" FOREIGN KEY (titre_id) REFERENCES
stock(stock_id)
"ventes_vendu_a_fkey" FOREIGN KEY (vendu_a) REFERENCES
clients(client_id)
Now I have a key 'vendu_a' (which is French for 'sold to') which links
to client_id in the clients table. Is this what has to be done every
time I want to link, or can I use the where clause ?
Thanks.
D.
> OK, now this is one concept I need explaining: do I > need to set up > 'links' from one table to another when I create the > tables? yes, if the tables relate to each other in some manner and you want to express it in your db. Here is > the 'clients' table of a new db that I've created > ... > > \d clients > Table "public.clients" > Column | Type | > Modifiers > ------------------+-----------------------+----------- > client_id | integer | not > null > nom | character varying(25) | not > null > prenom | character varying(25) | not > null > dob | date | > courriel | character varying(25) | not > null > numero_rue | numeric | not > null > nom_rue | character varying(50) | not > null > cp | character varying(6) | not > null > ville | character varying(25) | not > null > pays | character varying(10) | not > null > telephone | character varying(15) | not > null > date_inscription | date | > dernier_achat | date | > Indexes: > "clients_pkey" PRIMARY KEY, btree (client_id) > Check constraints: > "clients_check" CHECK (dernier_achat > > date_inscription) > > As you can see, there is a primary key which is the > client_id. Now the > 'purchases' table.. > > \d ventes > Table "public.ventes" > Column | Type | Modifiers > --------------+---------+----------- > vente_number | integer | not null > titre_id | integer | not null > prix_vente | numeric | not null > vendu_a | integer | not null > vendu_le | date | not null > Indexes: > "ventes_pkey" PRIMARY KEY, btree (vente_number) > Check constraints: > "ventes_vendu_le_check" CHECK (vendu_le > > '2005-12-31'::date) > Foreign-key constraints: > "ventes_titre_id_fkey" FOREIGN KEY (titre_id) > REFERENCES > stock(stock_id) > "ventes_vendu_a_fkey" FOREIGN KEY (vendu_a) > REFERENCES > clients(client_id) > > Now I have a key 'vendu_a' (which is French for > 'sold to') which links > to client_id in the clients table. Is this what has > to be done every > time I want to link, or can I use the where clause ? > > Thanks. > > D. DC, i *highly* recommend you get pgAdminIII or PHPpgAdmin. the best way to resolve these kinds of issues is to create sql and run it against your test data - especially when you are new. like i am. ;-) i thought you were joining three tables. you only listed two here. i'll give you a query i created to link three tables. SELECT notes.assembly_notes FROM t_product prod, t_link_product_assembly_notes link, t_assembly_notes notes WHERE link.product_id = 2 AND link.product_id = prod.product_id AND link.assembly_notes_id = notes.assembly_notes_id; i have three tables - one ids products, another ids notes and the third table links a note id to a product id. this allows me to link a single note to multiple products. i have three where clauses because they are all required to eliminate the repeating data you are now seeing. i wouldn't have figured this out except through trial and error inputting sql against my data. i'm not that sql smart yet. 1. my product id has to be 2 (in practice, this will be a variable whose value is taken from an application). 2. the product_id in the product table has to match the product_id in the link table, otherwise, i get notes unrealted to the product. 3. the assembly_notes_id has to match in the assembly notes table and the link table or else you will see repeating entries. for example, if i have three notes (ids 1,2,3) assigned to one product (ids 1), i will have three entries in assembly notes and three entries in my link table product 1 product 1 assembly notes 1 note 1 2 note 2 3 note 3 link 1 1 1 2 1 3 if i leave out where clause #3 (equality of assembly note ids in assembly note table and link table), i get the following cartesian result. an link 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3 by setting the equality rquirement, i'm left with what i want.... 1 1 2 2 3 3 again, trial and error will help you get the feel for what you need to know. get the ability to test sql against your db and compare the result with your needs. you may want to google some sql tutorials. heck, i will be doing that shortly since i'm a rookie, myself. i have a couple books on the subject, too. best of luck. Discover Yahoo! Use Yahoo! to plan a weekend, have fun online and more. Check it out! http://discover.yahoo.com/