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

In article <1992Mar4.000715.27244_at_pony.Ingres.COM> jabbate_at_lynx (Joe M. Abbate) writes:
>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).
>>
>>[deleted table descriptions]
>>
>>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.
>>
>>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.
>>
>>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
>

  I am going to try this but I don't think it works. Generally, most SQLs   handle views as merely syntactical poseurs for the actual SQL. When the   view is merged into a query, the original SQL code for the view is   usually used to create the parse and optimization tree. Therefore,   anything that is legal with a view is generally legal when written   out explicitly. Since you can't express this query explicitly, I   doubt you can express it through a view.

  Jon Received on Wed Mar 04 1992 - 20:46:12 CET