Re: SQL Puzzler - Join T and DISTINCT(T)

From: Joe M. Abbate <jabbate_at_lynx>
Date: 4 Mar 92 00:07:15 GMT
Message-ID: <1992Mar4.000715.27244_at_pony.Ingres.COM>


In article <matthew.699562055_at_pram.cs.ucla.edu>, matthew_at_pram.cs.ucla.edu (Ma writes...

>OK, all you SQL "wizards". I used to hope to count myself amongst your
>number, until I stumbled onto this little problem. This is *not* a
>homework problem (although it'd make a darned good one).
>
>First, I'm using Oracle, but this is a general SQL problem.
>
>I have a table, let's call it 'T', with two attributes (A & B), having the
>following values:
>
>A B
>---------
>1 x
>1 x
>1 x
>1 y
>1 y
>[ rest of table has other values for A - deleted as not germaine ]
>
>I want to join this table with its DISTINCT self on A, also ignoring cases
>where the DISTINCT B and the regular B column are identical.
>That is, I want to get:
>
>B B
>---------
>x y
>x y
>y x
>y x
>y x
>
>To recap:
>Let R = SELECT DISTINCT * from T
>
>Now, I want:
> SELECT R.B T.B
> WHERE R.A = T.A AND R.B != T.B
>
>However, I'd like to do these two steps in a *single* (presumably nested) SQL
>statement. It sounds easy, but it's turning into a nightmare.
>
>I can (and have) done it using ORACLE PL/SQL, by the way.
>
>Questions:
> 1. Is there a general single SQL statement that will join a table with
> it's distinct version?
>

I'm not sure if you'll consider this a *single* SQL statement solution, but ... if you first "create view r as select distinct a, b from t," then you can issue the following query to get your answer at any time:

	select r.b, t.b from r, t
	where r.a = t.a and r.b != t.b


Joe
jabbate_at_ingres.com Received on Wed Mar 04 1992 - 01:07:15 CET

Original text of this message