sub select vs. join

[blake]

Can anyone help convert the following sub-select into a join of some
kind that will return the results described below?

SELECT A.RST
FROM A
WHERE A.PROC = 'AAAA'
AND A.RST NOT IN
(
SELECT B.RST
FROM B
WHERE A. PROC = B.PROC
AND B.USERID = 'PGM'
)

A.RST can be 'O', 'P', 'Q', or 'R'.
B.RST is 'Q' or 'R'.
Valid result is 'O' and 'P'. PROC and RST are the only 'joinable'
columns in A and B tables

Thanks

[Ian D. Bjorhovde]

On 30 Jul 2003, blake wrote:

> Can anyone help convert the following sub-select into a join of some
> kind that will return the results described below?
>
> SELECT A.RST
> FROM A
> WHERE A.PROC = 'AAAA'
> AND A.RST NOT IN
> (
> SELECT B.RST
> FROM B
> WHERE A. PROC = B.PROC
> AND B.USERID = 'PGM'
> )
>
> A.RST can be 'O', 'P', 'Q', or 'R'.
> B.RST is 'Q' or 'R'.
> Valid result is 'O' and 'P'. PROC and RST are the only 'joinable'
> columns in A and B tables

If you have to join using the column RST then you have no choice
but to do a correlated subselect.







-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
[url]http://www.newsfeeds.com[/url] - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

[Tokunaga T.]

[email]blake_samples@labcorp.com[/email] (blake) wrote in message:

> Can anyone help convert the following sub-select into a join of some
> kind that will return the results described below?
>
> SELECT A.RST
> FROM A
> WHERE A.PROC = 'AAAA'
> AND A.RST NOT IN
> (
> SELECT B.RST
> FROM B
> WHERE A. PROC = B.PROC
> AND B.USERID = 'PGM'
> )
>
> A.RST can be 'O', 'P', 'Q', or 'R'.
> B.RST is 'Q' or 'R'.
> Valid result is 'O' and 'P'. PROC and RST are the only 'joinable'
> columns in A and B tables
>

Example1:
I could't understand why you want to use join.
Anyway here is an example, but only for my pleasure. This query must
have performance problem, and not so straightforward.
SELECT A.RST
FROM A
LEFT OUTER JOIN
B
ON A.PROC = B.PROC
AND A.RST = B.RST
AND B.USERID = 'PGM'
WHERE A.PROC = 'AAAA'
AND B.RST IS NULL
;

Example2:
You can use EXISTS predicate instead of IN predicate.
SELECT A.RST
FROM A
WHERE A.PROC = 'AAAA'
AND NOT EXISTS
(
SELECT *
FROM B
WHERE A.PROC = B.PROC
AND A.RST = B.RST
AND B.USERID = 'PGM'
)
;