Re: Interesting SQL Statement Problem...

Try this:

SELECT T1.col2, T2.col2
FROM tab1 T1, tab1 T2
WHERE T1.col1 = 1 AND T2.col1 = 2
UNION ALL
SELECT T1.col2, NULL
FROM tab1 T1 WHERE T1.col1 = 1 AND NOT EXISTS ( SELECT NULL FROM T2 WHERE col1 = 2)
UNION ALL
SELECT NULL, T2.col2
FROM tab1 T2 WHERE T2.col1 = 2 AND NOT EXISTS ( SELECT NULL FROM T1 WHERE col1 = 1)
;

Just a hunch, I haven't actually tried it !!!

TT

Marc Nichol <nmarc_at_NO.SPAM.execulink.com> schreef in berichtnieuws 397a8bd1.4117120_at_news1.on.sympatico.ca...
> Everyone
>
> Assume the following table exists:
>
> CREATE TABLE tab1
> (
> col1 NUMBER PRIMARY KEY,
> col2 VARCHAR2(40) NOT NULL
> );
>
> And assume the table contains the following data:
>
> COL1 COL2
>
> 1 "ONE"
> 2 "TWO"
> 4 "FOUR"
>
> I would like to select multiple records from the same table using only
> one select statement and returning only one row by doing something
> similar to this:
>
> SELECT T1.col2,
> T2.col2
> FROM tab1 T1,
> tab1 T2
> WHERE T1.col1 = 1
> AND T2.col1 = 2;
>
> The above query will successfully return only one row ("ONE" and
> "TWO") since both conditions of the WHERE clause evaluate to TRUE.
> However, my problem is that I would still like to return one row even
> if only one part of the WHERE clause is TRUE.
>
> For example, the following select statement returns no rows because
> "T2.col1 = 3" evaluates to FALSE:
>
> SELECT T1.col2,
> T2.col2
> FROM tab1 T1,
> tab1 T2
> WHERE T1.col1 = 1
> AND T2.col1 = 3;
>
> What I am trying to return is one row with "ONE" and NULL. Any
> thoughts would be greatly appreciated.
>
> Thanks!
Received on Sun Jul 23 2000 - 00:00:00 CEST