SQL Query - Mailing list pgsql-sql

From Miguel González
Subject SQL Query
Date
Msg-id 00ab01c13a9d$950761d0$1301a8c0@uimagen
Whole thread Raw
List pgsql-sql

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

 

 

 

pgsql-sql by date:

Previous
From: Kevin Way
Date:
Subject: table inheritance and foreign key troubles
Next
From: Christof Glaser
Date:
Subject: Re: table inheritance and foreign key troubles