Convert EXISTS to JOIN or?

Hello!
I have a query I cannot find a solution to.
Structure: Nodes transferring units. One nodes table with
info. It is related to itself, so that a node can have many
sourcenodes.
.--.
|xfers|--+
`--' |
| |
| .--+--. 1.* .--.
|--+nodes|--.intervals|
`--' `--'
The relation table is xfers (transfers). Each node has an
interval of a basic unit, a lot and start/stop.
I want access to:
- all nodes
- all sourcenodes that have an intersecting interval
(i e an interval with same lot and an intersecting
interval with the targetnode)
- the first matching intersecting interval for each node
I've been testing some different things but have not gotten
a working solution. This gives the correct rows but does
not get me access to the first matching intersecting
interval (T-SQL):
*** START SQL CODE:
-- 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
*** END SQL CODE
Notice use of intersection function, below.
No success in remaking this as a join so far.
Here is a setup script:
*** START SQL CODE:
CREATE TABLE nodes ( nodeid INT, nodename VARCHAR(32),
PRIMARY KEY( nodeid ) )
CREATE TABLE xfers ( target_nodeid INT, source_nodeid INT,
volume INT, PRIMARY KEY( source_nodeid, target_nodeid ) )
CREATE TABLE intervals ( nodes_nodeid INT, lot VARCHAR(3),
lotcount INT, start INT, stop INT, PRIMARY KEY(
nodes_nodeid, lot, lotcount ) )
go
INSERT INTO nodes
SELECT 12, 'Run A' UNION
SELECT 25, 'Switch A B' UNION
SELECT 26, 'Continue B' UNION
SELECT 57, 'Mix'
go
INSERT INTO intervals
SELECT 12, 'A', 1, 1, 180 UNION
SELECT 25, 'A', 1, 181, 195 UNION
SELECT 25, 'B', 1, 1, 21 UNION
SELECT 26, 'B', 1, 22, 492 UNION
SELECT 57, 'A', 1, 1, 195 UNION
SELECT 57, 'B', 1, 1, 492
go
INSERT INTO xfers
SELECT 57, 12, 15 UNION
SELECT 57, 25, 3 UNION
SELECT 57, 26, 28
go
*** END SQL CODE
Intersection function:
*** START SQL CODE:
CREATE FUNCTION Intersection ( @.startA int, @.stopA int,
@.startB int, @.stopB int )
RETURNS bit
AS
BEGIN
DECLARE @.rValue bit
IF
@.startA BETWEEN @.startB AND @.stopB
OR
@.stopA BETWEEN @.startB AND @.stopB
SET @.rValue = 1
ELSE
SET @.rValue = 0
RETURN ( @.rValue )
END
go
*** END SQL CODE
//mawimawi
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...
> Hello!
> I have a query I cannot find a solution to.
> Structure: Nodes transferring units. One nodes table with
> info. It is related to itself, so that a node can have many
> sourcenodes.
> .--.
> |xfers|--+
> `--' |
> | |
> | .--+--. 1.* .--.
> |--+nodes|--.intervals|
> `--' `--'
> The relation table is xfers (transfers). Each node has an
> interval of a basic unit, a lot and start/stop.
> I want access to:
> - all nodes
> - all sourcenodes that have an intersecting interval
> (i e an interval with same lot and an intersecting
> interval with the targetnode)
> - the first matching intersecting interval for each node
> I've been testing some different things but have not gotten
> a working solution. This gives the correct rows but does
> not get me access to the first matching intersecting
> interval (T-SQL):
> *** START SQL CODE:
> -- 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
> *** END SQL CODE
> Notice use of intersection function, below.
> No success in remaking this as a join so far.
> Here is a setup script:
> *** START SQL CODE:
> CREATE TABLE nodes ( nodeid INT, nodename VARCHAR(32),
> PRIMARY KEY( nodeid ) )
> CREATE TABLE xfers ( target_nodeid INT, source_nodeid INT,
> volume INT, PRIMARY KEY( source_nodeid, target_nodeid ) )
> CREATE TABLE intervals ( nodes_nodeid INT, lot VARCHAR(3),
> lotcount INT, start INT, stop INT, PRIMARY KEY(
> nodes_nodeid, lot, lotcount ) )
> go
> INSERT INTO nodes
> SELECT 12, 'Run A' UNION
> SELECT 25, 'Switch A B' UNION
> SELECT 26, 'Continue B' UNION
> SELECT 57, 'Mix'
> go
> INSERT INTO intervals
> SELECT 12, 'A', 1, 1, 180 UNION
> SELECT 25, 'A', 1, 181, 195 UNION
> SELECT 25, 'B', 1, 1, 21 UNION
> SELECT 26, 'B', 1, 22, 492 UNION
> SELECT 57, 'A', 1, 1, 195 UNION
> SELECT 57, 'B', 1, 1, 492
> go
> INSERT INTO xfers
> SELECT 57, 12, 15 UNION
> SELECT 57, 25, 3 UNION
> SELECT 57, 26, 28
> go
> *** END SQL CODE
> Intersection function:
> *** START SQL CODE:
> CREATE FUNCTION Intersection ( @.startA int, @.stopA int,
> @.startB int, @.stopB int )
> RETURNS bit
> AS
> BEGIN
> DECLARE @.rValue bit
> IF
> @.startA BETWEEN @.startB AND @.stopB
> OR
> @.stopA BETWEEN @.startB AND @.stopB
> SET @.rValue = 1
> ELSE
> SET @.rValue = 0
> RETURN ( @.rValue )
> END
> go
> *** END SQL CODE
> //mawi|||I haven't digested the whole thing, but this line doesn't seem right:
@.startA BETWEEN @.startB AND @.stopB
--^ and --^ @.startA / @.stopA surely?
"mawi" <anonymous@.discussions.microsoft.com> wrote in message
news:24d101c5344c$d11c2a50$a401280a@.phx.gbl...
> Hello!
> I have a query I cannot find a solution to.
> Structure: Nodes transferring units. One nodes table with
> info. It is related to itself, so that a node can have many
> sourcenodes.
> .--.
> |xfers|--+
> `--' |
> | |
> | .--+--. 1.* .--.
> |--+nodes|--.intervals|
> `--' `--'
> The relation table is xfers (transfers). Each node has an
> interval of a basic unit, a lot and start/stop.
> I want access to:
> - all nodes
> - all sourcenodes that have an intersecting interval
> (i e an interval with same lot and an intersecting
> interval with the targetnode)
> - the first matching intersecting interval for each node
> I've been testing some different things but have not gotten
> a working solution. This gives the correct rows but does
> not get me access to the first matching intersecting
> interval (T-SQL):
> *** START SQL CODE:
> -- 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
> *** END SQL CODE
> Notice use of intersection function, below.
> No success in remaking this as a join so far.
> Here is a setup script:
> *** START SQL CODE:
> CREATE TABLE nodes ( nodeid INT, nodename VARCHAR(32),
> PRIMARY KEY( nodeid ) )
> CREATE TABLE xfers ( target_nodeid INT, source_nodeid INT,
> volume INT, PRIMARY KEY( source_nodeid, target_nodeid ) )
> CREATE TABLE intervals ( nodes_nodeid INT, lot VARCHAR(3),
> lotcount INT, start INT, stop INT, PRIMARY KEY(
> nodes_nodeid, lot, lotcount ) )
> go
> INSERT INTO nodes
> SELECT 12, 'Run A' UNION
> SELECT 25, 'Switch A B' UNION
> SELECT 26, 'Continue B' UNION
> SELECT 57, 'Mix'
> go
> INSERT INTO intervals
> SELECT 12, 'A', 1, 1, 180 UNION
> SELECT 25, 'A', 1, 181, 195 UNION
> SELECT 25, 'B', 1, 1, 21 UNION
> SELECT 26, 'B', 1, 22, 492 UNION
> SELECT 57, 'A', 1, 1, 195 UNION
> SELECT 57, 'B', 1, 1, 492
> go
> INSERT INTO xfers
> SELECT 57, 12, 15 UNION
> SELECT 57, 25, 3 UNION
> SELECT 57, 26, 28
> go
> *** END SQL CODE
> Intersection function:
> *** START SQL CODE:
> CREATE FUNCTION Intersection ( @.startA int, @.stopA int,
> @.startB int, @.stopB int )
> RETURNS bit
> AS
> BEGIN
> DECLARE @.rValue bit
> IF
> @.startA BETWEEN @.startB AND @.stopB
> OR
> @.stopA BETWEEN @.startB AND @.stopB
> SET @.rValue = 1
> ELSE
> SET @.rValue = 0
> RETURN ( @.rValue )
> END
> go
> *** END SQL CODE
> //mawi|||Mercury wrote on Tue, 29 Mar 2005 23:59:16 +1200:

> I haven't digested the whole thing, but this line doesn't seem right:
> @.startA BETWEEN @.startB AND @.stopB
> --^ and --^ @.startA / @.stopA surely?
But that would result in the clause always being true.
The function appears to return a 1 if the A values are between the B values.
Dan