Re: tricky query question

From: Antony Sampson <sampson_at_seldon.apanix.apana.org.au>
Date: 11 Nov 1994 00:11:30 GMT
Message-ID: <39ucri$lf2_at_cleese.apana.org.au>

champs_at_cnb07v.hhcs.gov.au wrote:
: In article <khuisman.32.000E846A_at_wnet.gov.edmonton.ab.ca>, khuisman_at_wnet.gov.edmonton.ab.ca (Ken Huisman) writes:
: > I have a table with a 1:M relationship to another table.
: >
: > I would like to do a query on the first table based on whether or not there
: > exists related rows in the second table. The closest i have come is to just
: > do a join and retrieve the data on the 1 side several times - but i dont want
: > this because i only want to retrieve each row once.
: >
: > i would like something like this:
: >
: > select id from table1 where id is a member of ( select table1id from table2
: > where blah blah blah...)
: >
 

: There are at least 2 ways of doing this
 

: 1. select id from table1
: where id in (select table1id from table2)
 

: 2. select distinct table1.id from table1,table2
: where table1.id = table2.id
 

: > kind of a set membership kind of thing.
: >
: > Is there a way to do this?

Another way to do this which possibly better represents what you are trying to do is:

select columns_list
from table
where

.
.
.

and exists
(interior_query);

The outer query will only return a record if there is one or more records which match the where clause of interior_query;

The where clause of interior_query can refer to columns in the outer query, as in any other sub query.

Hope this helps,

---

Tony Sampson - alive and well and living in the state of South Australia. email to: sampson_at_apanix.apana.org.au

				TANSTAAFL, 
			 so I hope you're buying!

Received on Fri Nov 11 1994 - 01:11:30 CET