I am attempting to create something to keep track of purchase
requests and make sure they get approval by the proper supervisors.
Basically the way it works is by creating an entry in a table
(req_approvals) to link the user_id of the person who needs to
approve the request with the req_id and the status of that person's
approval (null, t, f).
I would like to find a way to create one query that will list all of
the items in req_req along with a column to show the status of the
entries in the req_approval table for that particular req_id. If any
of the approved entries in req_approval contain a f (request was
denied) then the column should return a f. If all of them return t
then the column should return t. If some of them are null AND none of
them are f then it should return null.
Is there an easy way to do this with one call to the db that I'm just
overlooking?
Thanks for any suggestions.
create table req_req (
req_id integer default nextval('req_req_seq')
primary key,
user_id integer references users,
description varchar(40),
creation_date date default CURRENT_DATE
);
create table req_approvals (
req_id integer references req_req,
user_id integer references users,
approved char(1) check (approved in ('t','f'))
);
