From: Miguel González <iafmgc@unileon.es>
To: Adam Lang <aalang@rutgersinsurance.com>
Cc: PostgreSQL PHP <pgsql-php@postgresql.org>
Cc: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: SQL Query
Date: Wed, 12 Sep 2001 09:51:46 +0200
Message-ID: <013f01c13b5f$c5c098b0$1301a8c0@uimagen> (raw)
References: <00ab01c13a9d$950761d0$1301a8c0@uimagen>
<002801c13ac0$ae8a0fe0$330a0a0a@rutgersinsurance.com>
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)
>
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, aalang@rutgersinsurance.com, pgsql-php@postgresql.org
Subject: Re: SQL Query
In-Reply-To: <013f01c13b5f$c5c098b0$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