pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcombining
3+ messages / 3 participants
[nested] [flat]
* combining
@ 2001-02-12 18:20 Frank Morton <fmorton@base2inc.com>
0 siblings, 2 replies; 3+ messages in thread
From: Frank Morton @ 2001-02-12 18:20 UTC (permalink / raw)
To: pgsql-sql
I'll really appreciate help on this if anyone will do so. I'm
used to single-table stuff, but not sure the best way to
do things with multiple tables, but here goes:
Given two tables with a common "id" field, first table columns:
id
lastname
Second table columns:
id
type
content
These two queries get me what I want from each table:
select unique id from table1 where lastname='morton';
select unique id from table2 where ((type = "pie") and (content = 'apple));
What is the best way to combine these into one query?
Thanks.
Frank
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: combining
@ 2001-02-12 18:46 Jie Liang <jliang@ipinc.com>
parent: Frank Morton <fmorton@base2inc.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jie Liang @ 2001-02-12 18:46 UTC (permalink / raw)
To: Frank Morton <fmorton@base2inc.com>; +Cc: pgsql-sql
Jie LIANG
St. Bernard Software
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
On Mon, 12 Feb 2001, Frank Morton wrote:
> I'll really appreciate help on this if anyone will do so. I'm
> used to single-table stuff, but not sure the best way to
> do things with multiple tables, but here goes:
>
> Given two tables with a common "id" field, first table columns:
>
> id
> lastname
>
> Second table columns:
>
> id
> type
> content
>
> These two queries get me what I want from each table:
>
> select unique id from table1 where lastname='morton';
> select unique id from table2 where ((type = "pie") and (content =
'apple));
it depends what you want:
1.
select id from table1 where lastname='morton' and id=table2.id
and table2.type = 'pie' and table2.content ='apple';
will return you the id in the intersection of 2 sets.
2.
select id from table1 where lastname='morton' union
select id from table2 where ((type = 'pie') and (content =
'apple'));
will return you a set1+set2;
>
> What is the best way to combine these into one query?
>
> Thanks.
>
> Frank
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: combining
@ 2001-02-12 19:01 Peter Eisentraut <peter_e@gmx.net>
parent: Frank Morton <fmorton@base2inc.com>
1 sibling, 0 replies; 3+ messages in thread
From: Peter Eisentraut @ 2001-02-12 19:01 UTC (permalink / raw)
To: Frank Morton <fmorton@base2inc.com>; +Cc: pgsql-sql
Frank Morton writes:
> These two queries get me what I want from each table:
>
> select unique id from table1 where lastname='morton';
> select unique id from table2 where ((type = "pie") and (content = 'apple));
>
> What is the best way to combine these into one query?
select table1.id from table1, table2 where table1.id = table2.id and
lastname = 'morton' and type='pie' and content = 'apple';
Or in 7.1, optionally:
select id from table1 natural join table2 where lastname = 'morton' and
type ='pie' and content = 'apple';
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-02-12 19:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-12 18:20 combining Frank Morton <fmorton@base2inc.com>
2001-02-12 18:46 ` Jie Liang <jliang@ipinc.com>
2001-02-12 19:01 ` Peter Eisentraut <peter_e@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox