Need to find multiple parents for a child

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Need to find multiple parents for a child

Author

Topic

bamabala
Starting Member

11 Posts

Posted - 2011-12-02 : 05:05:22

hi,

I have to find all the parents related a batch and also its child.

Here is the scenario,

In my table i have two columns

BatchID OldBatchID
8172 8083
8642 8172
8642 8265

Now i have only the batchid 8172. by passing this as input i need to find all the above values.

I tried like this

WITH Batch_CTE AS (
(
SELECT DISTINCT
TOH.[BatchID],
TOH.[OldBatchID],
level = 0
FROM
[BRD_IQD_PDB].[dbo].[tblIQTempOrderHeader] TOH
INNER JOIN #BatchIDs B ON TOH.OldBatchID = B.BatchID
)
UNION ALL
SELECT
TOH.[BatchID],
TOH.[OldBatchID],
level = level + 1
FROM
[BRD_IQD_PDB].[dbo].[tblIQTempOrderHeader] TOH
INNER JOIN Batch_CTE bcte ON TOH.[BatchID] = bcte.[OldBatchID]
)
SELECT * FROM Batch_CTE

But i am getting only the below result.
BatchID OldBatchID
8172 8083
8642 8172

Kindly help me to solve this issue.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-02 : 05:39:25

As you only want to go down one level in the hierarchy then (actually no need ofr a cte but

declare @t table (BatchID int, OldBatchID int)
insert @t select 8172, 8083
insert @t select 8642, 8172
insert @t select 8642, 8265
declare @BatchID int = 8172
;with cte as
(
select BatchID, OldBatchID from @t where BatchID = @BatchID
union
select BatchID, OldBatchID from @t where OldBatchID = @BatchID
)
select * from @t where BatchID in (select BatchID from cte)

BatchID OldBatchID
----------- -----------
8172 8083
8642 8172
8642 8265

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bamabala
Starting Member

11 Posts

Posted - 2011-12-02 : 06:59:45

thanks for your reply.

But the issue is the batchid 8642 has following as oldbatchids
7265,7772,7839,8172,8233,8234,8235,8238

These oldbatchids may be old batch to some other batches or it may have old batchid

So i need to find out all the batches and oldbatches.

BatchID OldBatchID
8172 8083
8083 If it has oldbacth
8642 8172
8642 7265
8642 7772
7265 If it has oldbacth
Batch 8642

I have to get the result like this.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-02 : 07:05:08

Not sure what you want.
Pass in 8172
get all the entries with 8172 as the BatchID
get all the BatchIDs with 8172 as the OldBatchID
get all the entries with those BatchIDs
get all the BatchIDs with those BatchIDs as the OldBatchID
...

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bamabala
Starting Member

11 Posts

Posted - 2011-12-02 : 07:14:35

I have need to pass 8172 as input and i need to get its oldbatchids and batchids and their corresponding batchids and oldnatchids and in turn.

I am not sure am i clarified my requirement.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-02 : 07:15:44

maybe

declare @t table (BatchID int, OldBatchID int)
insert @t select 8172, 8083
insert @t select 8642, 8172
insert @t select 8642, 7265
insert @t select 8642, 7772
insert @t select 7772, 0001
insert @t select 0001, 8172
declare @BatchID int = 8172
;with cte as
(
select BatchID, OldBatchID, seq = 0 from @t where BatchID = @BatchID
union
select BatchID, OldBatchID, seq = 1 from @t where OldBatchID = @BatchID
union all
select t.BatchID, t.OldBatchID, seq = seq+1 from cte join @t t on cte.OldBatchID = t.BatchID
)
select * from @t where BatchID in (select BatchID from cte)

BatchID OldBatchID
----------- -----------
8172 8083
8642 8172
8642 7265
8642 7772
1 8172

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bamabala
Starting Member

11 Posts

Posted - 2011-12-02 : 07:31:42

I have already tried the above query, but the batch 7265 has an oldbatch 7773.

So the result should contain the below row too.
Batch OldBatchID
7265 7773

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-02 : 07:59:52

Can't see that

declare @t table (BatchID int, OldBatchID int)
insert @t select 8172, 8083
insert @t select 8642, 8172
insert @t select 8642, 7265
insert @t select 8642, 7772
insert @t select 7772, 0001
insert @t select 0001, 8172

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bamabala
Starting Member

11 Posts

Posted - 2011-12-02 : 08:04:05

I have checked it in my original table.

declare @t table (BatchID int, OldBatchID int)
insert @t select 8172, 8083
insert @t select 8642, 8172
insert @t select 8642, 7265
insert @t select 8642, 7772
insert @t select 7772, 0001
insert @t select 0001, 8172
insert @t select 7265, 7773

check this its not giving the last record in the result.

bamabala
Starting Member

11 Posts

Posted - 2011-12-06 : 07:14:38

Kindly Can any one help to solve this issue...

bamabala
Starting Member

11 Posts

Posted - 2011-12-08 : 06:28:11

Hi,

I have found one solution for the above problem and it works fine.

declare @t table (BatchID int, OldBatchID int)
insert @t select 8172, 8083
insert @t select 8642, 8172
insert @t select 8642, 7265
insert @t select 8642, 7772
insert @t select 7772, 0001
insert @t select 7265, 0002
insert @t select 0001, 8172
declare @BatchID int = 8172
;WITH Batch_CTE AS
(
(
SELECT
[BatchID],
[OldBatchID]
FROM
@t
WHERE
[BatchID] = @BatchID
AND [OldBatchID] IS NOT NULL
UNION

SELECT
[BatchID],
[OldBatchID]
FROM
@t
WHERE
[OldBatchID] = @BatchID
)
UNION ALL
SELECT
T.[BatchID],
T.[OldBatchID]
FROM
@t T
INNER JOIN Batch_CTE bcte ON T.[OldBatchID] = bcte.[BatchID]
WHERE T.[OldBatchID] IS NOT NULL
),
Batch_CTE1 AS
(
SELECT
T.[BatchID],
T.[OldBatchID]
FROM
@t T
WHERE
EXISTS(
SELECT 1 FROM Batch_CTE BC WHERE BC.[BatchID] = T.[BatchID]
)
--TOH.BatchID IN(SELECT BatchID FROM Batch_CTE)
UNION ALL
SELECT
T.[BatchID],
T.[OldBatchID]
FROM
@t T
INNER JOIN Batch_CTE1 bcte ON bcte.[OldBatchID] = T.[BatchID]
WHERE T.[OldBatchID] IS NOT NULL
)
SELECT DISTINCT [BatchID],[OldBatchID] FROM Batch_CTE1 ORDER BY [OldBatchID]

bamabala
Starting Member

11 Posts

Posted - 2011-12-08 : 07:09:54

declare @t table (BatchID int, OldBatchID int)
insert @t select 8172, 8083
insert @t select 8642, 8172
insert @t select 8642, 7265
insert @t select 8642, 7772
insert @t select 7772, 0001
insert @t select 7265, 0002
insert @t select 0003, 7265
insert @t select 0003, 7266
insert @t select 0001, 8172

If i am having the above values in my table i could not get the correct answer.

Kindly help to work out this.

bamabala
Starting Member

11 Posts

Posted - 2011-12-12 : 02:23:40

i have found a soltion for this.

declare @t table (BatchID int, OldBatchID int)
insert @t select 8172, 8083
insert @t select 8642, 8172
insert @t select 8642, 7265
insert @t select 8642, 7772
insert @t select 7772, 0001
insert @t select 7265, 0002
insert @t select 0003, 7265
insert @t select 0003, 7266
insert @t select 0001, 8172
declare @BatchID int = 8172
BEGIN
DECLARE @rows INT = 0
CREATE TABLE #reached (
nodeID INT
)
CREATE UNIQUE CLUSTERED INDEX nodeID1 ON #reached(nodeID) WITH IGNORE_DUP_KEY
INSERT INTO #reached VALUES (@BatchID)
SET @rows = @@ROWCOUNT
WHILE @rows > 0
BEGIN
INSERT INTO #reached
SELECT DISTINCT BatchID
FROM @t AS e
INNER JOIN #reached AS p ON e.OldBatchID = p.nodeID
SET @rows = @@ROWCOUNT
INSERT INTO #reached
SELECT DISTINCT OldBatchID
FROM @t AS e
INNER JOIN #reached AS p ON e.BatchID = p.nodeID
SET @rows = @rows + @@ROWCOUNT
END
SELECT
BatchID,
OldBatchID
FROM
@t t1
INNER JOIN #reached r ON t1.BatchID = r.nodeID
UNION
SELECT
BatchID,
OldBatchID
FROM
@t t1
INNER JOIN #reached r ON t1.OldBatchID = r.nodeID
DROP TABLE #reached;
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources