From: Miguel González <iafmgc@unileon.es>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Cc: PostgreSQL PHP <pgsql-php@postgresql.org>
Subject: SQL Query
Date: Tue, 11 Sep 2001 10:41:42 +0200
Message-ID: <00ab01c13a9d$950761d0$1301a8c0@uimagen> (raw)
Dear all,
I want to do a query to my PostgreSQL database. I want to use it in a PHP web 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 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 item with the description provided by a form by the user.
That query works fine, it is just a simple join among cdroms, items and cdroms_items.
My problem shows up when I want to create two new fields to show whether the cdrom is on loan or is booked (someone made a reservation).
After reading some books and web sites, I concluded to use the CASE statement ( 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 reservations tables, if there is not, the back-end says that you are trying to get more
than 200 registers. What am I doing wrong? Here you have the SQL query. I 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:
SELECT
cdroms.code_cdroms,cdroms_etiqueta,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_prestamo
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_loans
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%';
Sorry for my English. Hope you can understand. Is is a problem of concept or a sintax problem?
Many thanks in advance
Regards
Miguel
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iafmgc@unileon.es, pgsql-php@postgresql.org
Subject: Re: SQL Query
In-Reply-To: <00ab01c13a9d$950761d0$1301a8c0@uimagen>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox