Thread: concepts?

concepts?

From
D.C.
Date:
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.


Re: concepts?

From
Date:
--- "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

Re: concepts?

From
Andres Ledesma
Date:
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/

Re: concepts?

From
Tom Lane
Date:
"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

Re: concepts?

From
Stephan Szabo
Date:
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.

Problem with Out-of-resources error?

From
cbrazvan@laitek.com
Date:
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



Re: Problem with Out-of-resources error?

From
tgoodaire@linux.ca (Tim Goodaire)
Date:
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)
>

Attachment

Re: Problem with Out-of-resources error?

From
Razvan Costea-Barlutiu
Date:
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.



Re: Problem with Out-of-resources error?

From
"Buddy Shearer"
Date:
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)
>


Re: Problem with Out-of-resources error?

From
tgoodaire@linux.ca (Tim Goodaire)
Date:
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
>

Attachment

Re: Problem with Out-of-resources error?

From
D.C.
Date:
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



Re: Problem with Out-of-resources error?

From
Razvan Costea-Barlutiu
Date:
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
>>



Re: concepts?

From
D.C.
Date:
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.

Re: concepts?

From
Date:
> 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/