Query Question - Mailing list pgsql-general

From Frodo Larik
Subject Query Question
Date
Msg-id 43391CD5.9000304@elasto.nl
Whole thread Raw
Responses Re: Query Question
List pgsql-general
Hi All,

I have the following simplified setup. A client has 2 products: 'vbp'
and 'year_balance', but a client has also workers who have a product,
named 'ib'. A client can have multiple workers.

-- clients
CREATE TABLE clients (
    id serial NOT NULL PRIMARY KEY,
    name text NOT NULL,
    vbp boolean DEFAULT 'f'::bool NOT NULL,  -- product 'vbp'
    year_balance boolean DEFAULT 'f'::bool NOT NULL    -- product
'year_balance'
);


-- workers
CREATE TABLE workers (
    id serial NOT NULL PRIMARY KEY,
    client_id integer NOT NULL REFERENCES clients(id),
    ib boolean DEFAULT 'f'::bool NOT NULL   -- product 'ib'
);


There one thing I like to know. What products are active for a client
(clients.id) or for all clients:
I don't know what query I can use to accomplish this, but I know I would
like to have results like this

SELECT <<some_query_magic>>

clients.name | workers.ib | clients.vbp | clients.year_balance
----------------+--------------+--------------+-------------------------
 client a        |  t                |   f               | f
 client b        |  f                | t                 |  t


It is possible that a client has zero or more workers, I want to know if
one of the workers has workers.ib = 't' set if this is true I like to
have 't' returned else a 'f'

Is this possible in a single query?


Sincerely,

Frodo Larik





pgsql-general by date:

Previous
From: Gábor Farkas
Date:
Subject: Re: change db encoding?
Next
From: boinger
Date:
Subject: Performance woes relating to DISTINCT (I think)