Thread: combining

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

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
> 
> 

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/