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