PostgreSQL Discussion - Query Question

Author

Query Question

Frodo Larik

2005-09-27, 7:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Jim C. Nasby

2005-09-29, 8:23 pm

SELECT c.name, w.ib, c.vbp, c.year_balance
FROM clients c
LEFT JOIN workers w ON (w.client_id = c.id)
;

Will do the trick.

BTW, I suggest not using 'bareword' id's for field names. It's very easy
to get confused with larger queries. So instead of clients.id, do
clients.client_id. I do the same thing with name, since it's also a very
common field name, so instead of clients.name, clients.client_name.

Also, most people don't use pluralized table names. I'm guessing you're
using Ruby on Rails here...

On Tue, Sep 27, 2005 at 12:20:05PM +0200, Frodo Larik wrote:
> 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
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links