Posted by Serge Rielau on 01/24/08 17:01

Hiho wrote:
>> SELECT x, y
>> FROM Foo AS F
>> CROSS APPLY (SELECT y FROM Bar AS B1 WHERE B1.x = F.x
>> UNION
>> SELECT y FROM Bar AS B2 WHERE B2.x = F.x) AS B
>> WHERE y IS NOT NULL;
>
> Yeah, that's it !
> But I'm under SQL Server 2000.... I think I'll keep the first
> solution.
>
> S. Rielau : the problem is the "WHERE B1.x=F.x".
> If I write SELECT X FROM F, (SELECT ... FROM B1 WHERE B1.x=F.x) AS F1
> SQL Server doesn't recognize F.x.
That's not what I wrote.
You can put the WHERE on the outer SELECT.

Teh SQL Standard to make what you tried work is:
FROM X, LATERAL(SELECT ..... WHERE X.blah = ...)
It's called lateral correlation.
Incidently CROSS APPLY Seems to be a funny invention to do SQL Standard:
CROSS JOIN LATERAL(...).

Vendor lock in SQL dialect for no good reason... grmbl grmbl...
Where is Celko when he's needed. ;-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Back to original message]