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