pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
combining
3+ messages / 3 participants
[nested] [flat]

* combining
@ 2001-02-12 18:20 Frank Morton <fmorton@base2inc.com>
  2001-02-12 18:46 ` Re: combining Jie Liang <jliang@ipinc.com>
  2001-02-12 19:01 ` Re: combining Peter Eisentraut <peter_e@gmx.net>
  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:20 combining Frank Morton <fmorton@base2inc.com>
@ 2001-02-12 18:46 ` Jie Liang <jliang@ipinc.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 18:20 combining Frank Morton <fmorton@base2inc.com>
@ 2001-02-12 19:01 ` Peter Eisentraut <peter_e@gmx.net>
  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