pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL Query
3+ messages / 2 participants
[nested] [flat]

* SQL Query
@ 2001-09-11 08:41 Miguel González <iafmgc@unileon.es>
  2001-09-11 12:52 ` Re: SQL Query Adam Lang <aalang@rutgersinsurance.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Miguel González @ 2001-09-11 08:41 UTC (permalink / raw)
  To: PostgreSQL SQL <pgsql-sql@postgresql.org>; +Cc: pgsql-php

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: SQL Query
  2001-09-11 08:41 SQL Query Miguel González <iafmgc@unileon.es>
@ 2001-09-11 12:52 ` Adam Lang <aalang@rutgersinsurance.com>
  2001-09-12 07:51   ` Re: SQL Query Miguel González <iafmgc@unileon.es>
  0 siblings, 1 reply; 3+ messages in thread

From: Adam Lang @ 2001-09-11 12:52 UTC (permalink / raw)
  To: ; +Cc: pgsql-php

If you could give the table layouts a little bit better, I may be able to
help.  I can't quite tell what the table names are and what the fields are.

As a preliminary guess, it almost looks like you don't have fields in the
other tables to properly link thedata... but again I can't tell for sure.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Miguel González" <iafmgc@unileon.es>



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: SQL Query
  2001-09-11 08:41 SQL Query Miguel González <iafmgc@unileon.es>
  2001-09-11 12:52 ` Re: SQL Query Adam Lang <aalang@rutgersinsurance.com>
@ 2001-09-12 07:51   ` Miguel González <iafmgc@unileon.es>
  0 siblings, 0 replies; 3+ messages in thread

From: Miguel González @ 2001-09-12 07:51 UTC (permalink / raw)
  To: Adam Lang <aalang@rutgersinsurance.com>; +Cc: pgsql-php; PostgreSQL SQL <pgsql-sql@postgresql.org>


Sorry If i didnt make clear which are the fields and which are the tables.
The tables are the ones "underlined", so I have cdroms, items, cdroms_items,
loans and reservations. I just left the important
fields in order not to confuse the people, and just wrote the name of the
fields that are the primary keys and also the foreign keys.

An item can be in different cdroms, and a cdrom can have different items,
thats why i have the table cdroms_items for the n to n relation. I forgot to
put the name of some fields when I translated into English, I am sorry about
it. In the tables loans and reservations i added a field called typetable, a
string where i store wich kind of object is loaned or booked, this is
because I also have a hardware table, where i store names of different
hardware that also could be loaned or booked. The idea is that the loanable
and bookable objets and therefore the number of tables will be increased
(books,magazines, etc).

I reviewed the query and i noticed that I didnt translate well some names,
sorry again. Hope that now you can help me out, i am getting close to the
dateline.

Many thanks  Miguel


---------------- Reviewed information  ----------------------------


In loans i

cdroms
-------
code_cdroms
label

items
-----
code_items
description


cdroms_items
-------------
code_cdroms
code_items

loans
----------
code_loan
typetable


reservations
--------
code_reservation
typetable




SELECT

cdroms.code_cdroms,cdroms_label,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_loan
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_loan
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%';




----- Original Message -----
From: "Adam Lang" <aalang@rutgersinsurance.com>
Cc: "PostgreSQL PHP" <pgsql-php@postgresql.org>
Sent: Tuesday, September 11, 2001 2:52 PM
Subject: Re: [PHP] SQL Query


> If you could give the table layouts a little bit better, I may be able to
> help.  I can't quite tell what the table names are and what the fields
are.
>
> As a preliminary guess, it almost looks like you don't have fields in the
> other tables to properly link thedata... but again I can't tell for sure.
>
> Adam Lang
> Systems Engineer
> Rutgers Casualty Insurance Company
> http://www.rutgersinsurance.com
> ----- Original Message -----
> From: "Miguel González" <iafmgc@unileon.es>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-09-12 07:51 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-11 08:41 SQL Query Miguel González <iafmgc@unileon.es>
2001-09-11 12:52 ` Adam Lang <aalang@rutgersinsurance.com>
2001-09-12 07:51   ` Miguel González <iafmgc@unileon.es>

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