Convert EXISTS to JOIN or? Reformulated!

Hello all!
Thanks for the followups. I think I was too unclear: The
function was not the focus, more an appendix to the setup.
So run the setup and the function.
My question was how to fix the query so that I can access
the matching row that makes the EXISTS true. Either via a
rewrite of the EXISTS condition using a join (which I have
attempted but ultimately failed) or something else, like
TOP 1 on the combined xfers / intervals set.
This query returns the correct result, yet I do not have
access to the (first found) matching row in the EXISTS:
-- Desired rows, yet I cannot access the matching row in
the EXISTS test
SELECT
nodes.nodeid, nodes.nodename,
source_nodeid, volume
FROM nodes
LEFT OUTER JOIN xfers
ON target_nodeid = nodes.nodeid AND EXISTS
( SELECT *
FROM intervals sourceIntervals
JOIN intervals nodeIntervals
ON
nodeIntervals.lot = sourceIntervals.lot
AND dbo.Intersection( nodeIntervals.start,
nodeIntervals.stop, sourceIntervals.start,
sourceIntervals.stop ) = 1
WHERE
sourceIntervals.nodes_nodeid = xfers.source_nodeid
AND nodeIntervals.nodes_nodeid = xfers.target_nodeid
)
LEFT OUTER JOIN nodes sourceNodes
ON sourceNodes.nodeid = source_nodeid
Here is the setup script again, nicely formatted:
http://authors.aspalliance.com/ayla...asteCodeID=4019
Here is the problem query and one of my feeble attempts:
http://authors.aspalliance.com/ayla...asteCodeID=4020
Ali: The case is nicer, thanks.
/mawi
>--Original Message--
>mawi
>What do you want to return? What is an output?
>I'd rewrite a little bit the function
>CREATE FUNCTION Intersection ( @.startA int, @.stopA int,
> @.startB int, @.stopB int )
> RETURNS bit
> AS
> BEGIN
> RETURN CASE
> WHEN @.startA BETWEEN @.startB AND @.stopB THEN
1 ELSE 0 END
> END
> go
>
>"mawi" <anonymous@.discussions.microsoft.com> wrote in message
>news:24d101c5344c$d11c2a50$a401280a@.phx.gbl...
>
>.
>mawi
You cannot specify columns as parameters to the UDF.
Perhasp you want to use a CASE expression to compare the dates.
"mawi" <anonymous@.discussions.microsoft.com> wrote in message
news:259101c53462$4ed023b0$a401280a@.phx.gbl...
> Hello all!
> Thanks for the followups. I think I was too unclear: The
> function was not the focus, more an appendix to the setup.
> So run the setup and the function.
> My question was how to fix the query so that I can access
> the matching row that makes the EXISTS true. Either via a
> rewrite of the EXISTS condition using a join (which I have
> attempted but ultimately failed) or something else, like
> TOP 1 on the combined xfers / intervals set.
> This query returns the correct result, yet I do not have
> access to the (first found) matching row in the EXISTS:
> -- Desired rows, yet I cannot access the matching row in
> the EXISTS test
> SELECT
> nodes.nodeid, nodes.nodename,
> source_nodeid, volume
> FROM nodes
> LEFT OUTER JOIN xfers
> ON target_nodeid = nodes.nodeid AND EXISTS
> ( SELECT *
> FROM intervals sourceIntervals
> JOIN intervals nodeIntervals
> ON
> nodeIntervals.lot = sourceIntervals.lot
> AND dbo.Intersection( nodeIntervals.start,
> nodeIntervals.stop, sourceIntervals.start,
> sourceIntervals.stop ) = 1
> WHERE
> sourceIntervals.nodes_nodeid = xfers.source_nodeid
> AND nodeIntervals.nodes_nodeid = xfers.target_nodeid
> )
> LEFT OUTER JOIN nodes sourceNodes
> ON sourceNodes.nodeid = source_nodeid
> Here is the setup script again, nicely formatted:
> http://authors.aspalliance.com/ayla...asteCodeID=4019
> Here is the problem query and one of my feeble attempts:
> http://authors.aspalliance.com/ayla...asteCodeID=4020
>
> Ali: The case is nicer, thanks.
> /mawi
> 1 ELSE 0 END|||
>--Original Message--
>mawi
>You cannot specify columns as parameters to the UDF.
>Perhasp you want to use a CASE expression to compare the
dates.
True. But nevermind the function, it is just a shorthand
and works fine as it is.
How about that query, any ideas?

http://authors.aspalliance.com/ayla...asteCodeID=4019
http://authors.aspalliance.com/ayla...asteCodeID=4020
message
have many
gotten
INT,
VARCHAR(3),
>
>.
>|||mawi
There will be some ideas if post desired output.
<anonymous@.discussions.microsoft.com> wrote in message
news:0bef01c53469$caddc870$a501280a@.phx.gbl...
>
> dates.
> True. But nevermind the function, it is just a shorthand
> and works fine as it is.
> How about that query, any ideas?
>
> http://authors.aspalliance.com/ayla...asteCodeID=4019
> http://authors.aspalliance.com/ayla...asteCodeID=4020
> message
> have many
> gotten
> INT,
> VARCHAR(3),