From iafmgc@unileon.es Tue Sep 11 08:45:42 2001 Received: from isidoro2.unileon.es (isidoro2.unileon.es [193.146.96.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8B8jg757580; Tue, 11 Sep 2001 04:45:42 -0400 (EDT) (envelope-from iafmgc@unileon.es) Received: from intergraph (n104111.unileon.es [193.146.104.111]) by isidoro2.unileon.es (8.9.3/8.9.1) with SMTP id KAA19474; Tue, 11 Sep 2001 10:45:40 +0200 (MET DST) Message-ID: <00ab01c13a9d$950761d0$1301a8c0@uimagen> From: =?iso-8859-1?Q?Miguel_Gonz=E1lez?= To: "PostgreSQL SQL" Cc: "PostgreSQL PHP" Subject: SQL Query Date: Tue, 11 Sep 2001 10:41:42 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00A8_01C13AAE.585BB450" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200109/5 X-Sequence-Number: 526 This is a multi-part message in MIME format. ------=_NextPart_000_00A8_01C13AAE.585BB450 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Dear all,=20 I want to do a query to my PostgreSQL database. I want to use it in a PHP w= eb client that I have got, but also=20 I have tried in the back-end.=20 I have the following tables on my database ( I translate the field names fr= om Spanish into English) cdroms ------- code_cdroms items ----- code_items description cdroms_items ------------- code_cdroms code_items=20 loans ---------- code_loan reservations -------- code_reservation The Query I want to execute is to search the cdroms that contains an item w= ith the description provided by a form by the user.=20 That query works fine, it is just a simple join among cdroms, items and cdr= oms_items.=20 My problem shows up when I want to create two new fields to show whether th= e cdrom is on loan or is booked (someone made a reservation).=20 After reading some books and web sites, I concluded to use the CASE stateme= nt ( I do not know if there is any other alternative). I tried it, and it= =20 worked, but only in the case there is any register in the loans or reservat= ions tables, if there is not, the back-end says that you are trying to get = more=20 than 200 registers. What am I doing wrong? Here you have the SQL query. I t= ranslated the name of the fields, hope there is no mistake.=20 I search for a string 'net' in the description field of the items table:=20 SELECT cdroms.code_cdroms,cdroms_etiqueta,items.description,loans.code_loans,=20 loans.tabletype,reservations.code_reservations,reservations.tabletype, (case when cdroms.code_cdroms=3Dcdroms_items.code_cdroms and cdroms.code_cdroms=3Dcode_prestamo and cdroms_items.code_items=3Ditems.code_items and items.description like %net% then 'Yes' else 'No' end) as onloan, (case when cdroms.code_cdroms=3Dcdroms_items.code_cdroms and cdroms.code_cdroms=3Dcode_loans and cdroms.code_cdroms=3D ANY (select code_reservations from reservations) and cdroms_items.code_items=3Ditems.code_items and items.description like %net% then 'Yes' else 'No' end) as booked from cdroms,items,cdroms_items,loans,reservations where cdroms.code_cdroms=3Dcdroms_items.code_cdroms and loans.tabletype=3D'cdroms' and cdroms_items.code_items=3Ditems.code_items and items.description like '%net%'; Sorry for my English. Hope you can understand. Is is a problem of concept o= r a sintax problem? Many thanks in advance Regards Miguel ------=_NextPart_000_00A8_01C13AAE.585BB450 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Dear all,

I want to do a query to my PostgreSQL database. I want to use it in a PH= P web=20 client that I have got, but also

I have tried in the back-end.

I have the following tables on my database ( I translate the field names= from=20 Spanish into English)

cdroms

-------

code_cdroms

items

-----

code_items

description

cdroms_items

-------------

code_cdroms

code_items

loans

----------

code_loan

reservations

--------

code_reservation

The Query I want to execute is to search the cdroms that contains an ite= m=20 with the description provided by a form by the user.

That query works fine, it is just a simple join among cdroms, items and= =20 cdroms_items.

My problem shows up when I want to create two new fields to show whether= the=20 cdrom is on loan or is booked (someone made a reservation).

After reading some books and web sites, I concluded to use the CASE stat= ement=20 ( I do not know if there is any other alternative). I tried it, and it

worked, but only in the case there is any register in the loans or=20 reservations tables, if there is not, the back-end says that you are trying= to=20 get more

than 200 registers. What am I doing wrong? Here you have the SQL query. = I=20 translated the name of the fields, hope there is no mistake.

I search for a string 'net' in the description field of the items table:= =20

SELECT

cdroms.code_cdroms,cdroms_etiqueta,items.description,loans.code_lo= ans,=20

loans.tabletype,reservations.code_reservations,reservations.tablet= ype,

(case

when

cdroms.code_cdroms=3Dcdroms_items.code_cdroms

and=20 cdroms.code_cdroms=3Dcode_prestamo

and=20 cdroms_items.code_items=3Ditems.code_items

and items.description like %net%

then 'Yes' else 'No'

end)

as onloan,

(case

when

cdroms.code_cdroms=3Dcdroms_items.code_cdroms

and cdroms.code_cdroms=3Dcode_loans<= /FONT>

and cdroms.code_cdroms=3D ANY (selec= t=20 code_reservations from

reservations)

and=20 cdroms_items.code_items=3Ditems.code_items

and items.description like %net%

then 'Yes' else 'No'

end)

as booked

from=20 cdroms,items,cdroms_items,loans,reservations

where

cdroms.code_cdroms=3Dcdroms_items.code_cdroms

and loans.tabletype=3D'cdroms'

and=20 cdroms_items.code_items=3Ditems.code_items

and items.description like=20 '%net%';

Sorry for my English. Hope you can understand. Is is a problem of concep= t or=20 a sintax problem?

Many thanks in advance

Regards

Miguel

 

 

 

------=_NextPart_000_00A8_01C13AAE.585BB450-- From aalang@rutgersinsurance.com Tue Sep 11 12:54:10 2001 Received: from ford.rutgersinsurance.com ([38.138.71.203]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8BCsA730019 for ; Tue, 11 Sep 2001 08:54:10 -0400 (EDT) (envelope-from aalang@rutgersinsurance.com) Received: (from root@localhost) by ford.rutgersinsurance.com (8.11.2/8.11.2) id f8BCe3n09357 for pgsql-php@postgresql.org.KAV; Tue, 11 Sep 2001 08:40:03 -0400 Received: from nj012 ([10.10.10.51]) by ford.rutgersinsurance.com (8.11.2/8.11.2) with SMTP id f8BCe3p09349 for ; Tue, 11 Sep 2001 08:40:03 -0400 Message-ID: <002801c13ac0$ae8a0fe0$330a0a0a@rutgersinsurance.com> From: "Adam Lang" Cc: "PostgreSQL PHP" References: <00ab01c13a9d$950761d0$1301a8c0@uimagen> Subject: Re: SQL Query Date: Tue, 11 Sep 2001 08:52:57 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200109/6 X-Sequence-Number: 527 If you could give the table layouts a little bit better, I may be able to help. I can't quite tell what the table names are and what the fields are. As a preliminary guess, it almost looks like you don't have fields in the other tables to properly link thedata... but again I can't tell for sure. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Miguel González" From iafmgc@unileon.es Wed Sep 12 08:01:06 2001 Received: from isidoro2.unileon.es (isidoro2.unileon.es [193.146.96.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8C816768048; Wed, 12 Sep 2001 04:01:06 -0400 (EDT) (envelope-from iafmgc@unileon.es) Received: from intergraph (n104111.unileon.es [193.146.104.111]) by isidoro2.unileon.es (8.9.3/8.9.1) with SMTP id JAA12789; Wed, 12 Sep 2001 09:55:45 +0200 (MET DST) Message-ID: <013f01c13b5f$c5c098b0$1301a8c0@uimagen> From: =?iso-8859-1?Q?Miguel_Gonz=E1lez?= To: "Adam Lang" Cc: "PostgreSQL PHP" , "PostgreSQL SQL" References: <00ab01c13a9d$950761d0$1301a8c0@uimagen> <002801c13ac0$ae8a0fe0$330a0a0a@rutgersinsurance.com> Subject: Re: SQL Query Date: Wed, 12 Sep 2001 09:51:46 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200109/7 X-Sequence-Number: 528 Sorry If i didnt make clear which are the fields and which are the tables. The tables are the ones "underlined", so I have cdroms, items, cdroms_items, loans and reservations. I just left the important fields in order not to confuse the people, and just wrote the name of the fields that are the primary keys and also the foreign keys. An item can be in different cdroms, and a cdrom can have different items, thats why i have the table cdroms_items for the n to n relation. I forgot to put the name of some fields when I translated into English, I am sorry about it. In the tables loans and reservations i added a field called typetable, a string where i store wich kind of object is loaned or booked, this is because I also have a hardware table, where i store names of different hardware that also could be loaned or booked. The idea is that the loanable and bookable objets and therefore the number of tables will be increased (books,magazines, etc). I reviewed the query and i noticed that I didnt translate well some names, sorry again. Hope that now you can help me out, i am getting close to the dateline. Many thanks Miguel ---------------- Reviewed information ---------------------------- In loans i cdroms ------- code_cdroms label items ----- code_items description cdroms_items ------------- code_cdroms code_items loans ---------- code_loan typetable reservations -------- code_reservation typetable SELECT cdroms.code_cdroms,cdroms_label,items.description,loans.code_loans, loans.tabletype,reservations.code_reservations,reservations.tabletype, (case when cdroms.code_cdroms=cdroms_items.code_cdroms and cdroms.code_cdroms=code_loan and cdroms_items.code_items=items.code_items and items.description like %net% then 'Yes' else 'No' end) as onloan, (case when cdroms.code_cdroms=cdroms_items.code_cdroms and cdroms.code_cdroms=code_loan and cdroms.code_cdroms= ANY (select code_reservations from reservations) and cdroms_items.code_items=items.code_items and items.description like %net% then 'Yes' else 'No' end) as booked from cdroms,items,cdroms_items,loans,reservations where cdroms.code_cdroms=cdroms_items.code_cdroms and loans.tabletype='cdroms' and cdroms_items.code_items=items.code_items and items.description like '%net%'; ----- Original Message ----- From: "Adam Lang" Cc: "PostgreSQL PHP" Sent: Tuesday, September 11, 2001 2:52 PM Subject: Re: [PHP] SQL Query > If you could give the table layouts a little bit better, I may be able to > help. I can't quite tell what the table names are and what the fields are. > > As a preliminary guess, it almost looks like you don't have fields in the > other tables to properly link thedata... but again I can't tell for sure. > > Adam Lang > Systems Engineer > Rutgers Casualty Insurance Company > http://www.rutgersinsurance.com > ----- Original Message ----- > From: "Miguel González" > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >