Обсуждение: Combining queries

Hi.

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.

Thanks,

Mark

Sean Davis wrote on 19.02.2011 14:47:
>
>
> On Sat, Feb 19, 2011 at 8:25 AM, Mark Kelly <pgsql@wastedtimes.net <mailto:pgsql@wastedtimes.net>> wrote:
>
>     Hi.
>
>     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.
>
>
> Hi, Mark.
>
> Take a look at sql UNION--this is not postgresql specific.
>
More precisely: UNION ALL to avoid the redundant step that tries to eliminate duplicates

Regards
Thomas

Hi.

On Saturday 19 Feb 2011 at 16:14 Thomas Kellerer wrote:

> Sean Davis wrote on 19.02.2011 14:47:
[snip]
> > Take a look at sql UNION--this is not postgresql specific.
>
> More precisely: UNION ALL to avoid the redundant step that tries to
>  eliminate duplicates

Thank you both for taking the time to reply - I am off to do some reading now
that I know what to look for.

Mark

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.

Hi Lew.

On Sunday 20 Feb 2011 at 19:13 Lew wrote:
> One trusts that the result columns in the two tables have compatible types.

Yes, the column definitions all match.

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

The IDs have distinct formats that differ between tables, created by the
legacy system that originated the records (P-nnnn and C-nnnn). Each row also
has an integer primary key that my code uses internally, but I don't need it
in these results.

Thanks for the help, I'm looking forward to playing with UNION ALL when I go
back to work tomorrow, and your tip about eliminating the test is interesting.

Cheers,

Mark

Mark Kelly wrote:
> Thanks for the help, I'm looking forward to playing with UNION ALL when I go
> back to work tomorrow, and your tip about eliminating the test is interesting.

To be precise, I didn't propose to eliminate the tests, just the redundant
expressions within them.

--
Lew
Honi soit qui mal y pense.