Skip site navigation (1) Skip section navigation (2)

Re: Combining queries

From: Lew <noone(at)lewscanon(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Combining queries
Date: 2011-02-20 19:13:32
Message-ID: ijrp4k$odp$1@news.albasani.net (view raw or flat)
Thread:
Lists: pgsql-novice
Mark Kelly wrote:
> If I have two queries:
>
> SELECT pub_id as id, email FROM publication WHERE email_accepted = TRUE;
>
> and
>
> SELECT contact_id as id, email FROM person WHERE email_accepted = TRUE;
>
> is there any way I could combine these into a single query? I'd like to be
> able to create a view of all email-able entities in the system.

Sean Davis wrote:
> Take a look at sql UNION--this is not postgresql specific.

Thomas Kellerer wrote:
> More precisely: UNION ALL to avoid the redundant step that tries to eliminate
> duplicates

One may eliminate the redundant test of a truth value against a truth value.

SELECT pub_id as id, email FROM publication WHERE email_accepted
UNION ALL
SELECT contact_id as id, email FROM person WHERE email_accepted ;

One trusts that the result columns in the two tables have compatible types.

I am curious how you interpret the "id" result in that query.

-- 
Lew
Honi soit qui mal y pense.

In response to

Responses

pgsql-novice by date

Next:From: Mark KellyDate: 2011-02-21 00:31:52
Subject: Re: Combining queries
Previous:From: Jean-Yves F. BarbierDate: 2011-02-20 02:52:28
Subject: replication

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group