sql server programming:
Hello,
I've tinkered with this problem but have not found a solution.
(There is setup SQL below and my problem query)
*** Structure: Nodes transferring units. One nodes table.
It is related to itself, so that a nodes can supply another
node.
This relation (xfers) also has a volume, how much has each
node supplied the next with. Each node processes intervals
(of units).
.-----.
|xfers|----+
`-----' |
| |
| .--+--. 1.* .---------.
|-----+nodes|--------.intervals|
`-----' `---------'
*** Problem: I want to formulate a query that returns all
nodes, all sourcenodes of every node and one matching
interval for each sourcenode. I want to be able to sum up
the sourcenodes (xfers) transfer volumes correctly.
The problem is that the apparent query (example B) will (of
course) give me all sourcenodes and all matching intervals,
which may be more than one per sourcenode. This is not
interesting information, although correct. In the example:
Node 57 has node 25 as source. Node 25 has two intervals
that match node 57 intervals. I get two rows in the
resultset for the 57-25 relation (two transfer volumes
"3"). Correct per se, but now what I am after.
The desired rows are one for each sourcenode, and first
interval match. Example A satisfies the first condition but
does not let me display data from the first interval match.
Any help greatly appreciated.
/mawi
Correct rows, but not all columns:
nodeid nodename source_nodeid volume
------- ---------- ------------- -------
12 Run A NULL NULL
25 Switch A B NULL NULL
26 Continue B NULL NULL
57 Mix 12 15
57 Mix 25 3
57 Mix 26 28
Incorrect rows but some of the desired columns:
nodeid nodename source_nodeid volume start stop
------ ---------- ------------- ------ ----- -----
12 Run A NULL NULL NULL NULL
25 Switch A B NULL NULL NULL NULL
26 Continue B NULL NULL NULL NULL
57 Mix 12 15 1 180
57 Mix 25 3 181 195
57 Mix 25 3 1 21
57 Mix 26 28 22 492
Code is also here, setup:
http://authors.aspalliance.com/aylar/ViewPasteCode.aspx?PasteCodeID=4019 Problem queries:
http://authors.aspalliance.com/aylar/ViewPasteCode.aspx?PasteCodeID=4020 **** SQL CODE, setup:
-- mawi T-SQL EXISTS to JOIN problem: Setup script
create database IntervalXfers
go
use IntervalXfers
go
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
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
*** SQL CODE PROBLEMS:
-- 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
-- Test with join, one of many, not correct
SELECT
nodes.nodeid, nodes.nodename,
source_nodeid, volume, sourceIntervals.start,
sourceIntervals.stop
FROM nodes
LEFT OUTER JOIN
xfers
LEFT OUTER JOIN intervals sourceIntervals
ON sourceIntervals.nodes_nodeid = xfers.source_nodeid
JOIN intervals nodeIntervals
ON
nodeIntervals.nodes_nodeid = xfers.target_nodeid
AND nodeIntervals.lot = sourceIntervals.lot
AND dbo.Intersection( nodeIntervals.start,
nodeIntervals.stop, sourceIntervals.start,
sourceIntervals.stop ) = 1
ON target_nodeid = nodes.nodeid
LEFT OUTER JOIN nodes sourceNodes
ON sourceNodes.nodeid = source_nodeid
