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--