March 17, 2009 at 10:07 am
Hi friends,
My question is not related to JDBC but SQL.
I have dependency table as follows.
ColumnA ColumnB
A1 B1
A2 B1
A2 B2
A3 B1
A4 B1
A4 B2
A5 B1
A5 B2
A5 B3
A6 B2
A7 B4
Now I want to get all unique values from ColumnA and ColumnB for dependencies.
Let us say user wants dependency for A1.
here A1 > B1
so now I have to find dependency for B1.
B1 > A1,A2,A3,A4,A5 (I already have dependency for A1 so exclude from this result).
Now I have to find dependency for A2,A3,A4,A5 (unique)...
A2 > B1,B2
A3 > B1
A4 > B1,B2
A5 > B1,B2,B3
unique values from above output ----- A2,A3,A4,A5,B1,B2,B3 (A1,B1 is already included in second step of output)...
so again till now unique dependencies are.....
A1(search term),A2,A3,A4,A5,B1,B2,B3
remaining from above are B2,B3 so for B2,B3 now dependency is as below...
B2 > A2,A4,A5,A6
B3 > A5
(I have to exclude A1,A2,A3,A4,A5,B1,B2,B3 as it has already been part of the search )
so for A6
A6 > B2 (B2 is already a part of search, so exclude it from further search criteria)
so final dependencies are A2,A3,A4,A5,A6,B1,B2,B3. (A1 is first search parameter)
How can I implement this logic in any SQL.
If you guys have any other solution/suggestion then please guide. I do not want ready made code but some initial steps so I can do it myself.
Thanks for any help.
March 17, 2009 at 10:11 am
Have you tried looking up in BOL for Recursive CTE's?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 17, 2009 at 10:31 am
I am not expert in queries as of now.
Can you give some inputs so I go go ahead.
thanks
March 17, 2009 at 11:08 am
I do not understand why A2 is included but not A5. Are you able to reformulate the logic in different terms to help me figure this one out.
Thanks!
March 18, 2009 at 4:47 am
Sorry for any inconvenience. Thanks for correcting me.
I have reformulated the logic....please refer first edited post.
March 18, 2009 at 9:09 am
Hi there
This is a lot like a hierarchy except the circular references make the recursive query unusable.
I would reorganize your records into a temporary table with two columns (Search Term, Dependency) using a union so that you have a record for each possibility.
A1,B1 is also
B1,A1
And then loop insert...
Let me know if you need more details I can prepare an example if you want.
March 19, 2009 at 3:29 am
Here search terms are changing dynamically. Also search term values are from two different columns and are searched alternatively (First from column A then B then again A and so on....).
I think in temporary table creating three columns may make it more clear.
SearchTermFormColumnA,SearchTermFormColumnB,Dependency Values(A1,B1 etc...)
Do I have to use nested cursors in this case? How do I loop and provide loop conditions?
March 19, 2009 at 6:13 am
Good morning
I was thinking of reorganizing your data into only two columns like this:
--==== Temp table to simulate nike83's
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL
DROP TABLE #MyTable
CREATE TABLE #MyTable (
ID INT PRIMARY KEY IDENTITY(1,1)
,ColumnA CHAR(2)
,ColumnB CHAR(2)
)
--==== Test data
INSERT #MyTable (ColumnA,ColumnB)
SELECT 'A1', 'B1' UNION ALL
SELECT 'A2', 'B1' UNION ALL
SELECT 'A2', 'B2' UNION ALL
SELECT 'A3', 'B1' UNION ALL
SELECT 'A4', 'B1' UNION ALL
SELECT 'A4', 'B2' UNION ALL
SELECT 'A5', 'B1' UNION ALL
SELECT 'A5', 'B2' UNION ALL
SELECT 'A5', 'B3' UNION ALL
SELECT 'A6', 'B2' UNION ALL
SELECT 'A7', 'B4'
--==== Temp table to store Search Term Dependencies
IF OBJECT_ID('tempdb..#STD') IS NOT NULL
DROP TABLE #STD
CREATE TABLE #STD (
ID INT PRIMARY KEY IDENTITY(1,1)
,SearchTerm CHAR(2)
,Dependency CHAR(2)
)
--==== Reorganize data because any dependency
--==== can also be a search term
INSERT #STD (SearchTerm,Dependency)
SELECT
ColumnA AS SearchTerm
,ColumnB as Dependency
FROM #MyTable
UNION
SELECT
ColumnB AS SearchTerm
,ColumnA as Dependency
FROM #MyTable
--==== Review content
SELECT * FROM #STD
I was thinking of using a while loop but this could probably be resolved with nested cursors too. I don't think either solution will scale very well though. How is this going to be used? Would it be possible for you to retrieve the data from #STD and loop outside SQL Server?
--==== Table to store the dependencies
IF OBJECT_ID('tempdb..#Dependency') IS NOT NULL
DROP TABLE #Dependency
CREATE TABLE #Dependency (
SearchTerm CHAR(2)
)
--==== Node we want to start the search at
DECLARE @TreeStart CHAR(2)
SET @TreeStart = 'A1'
--==== Variable for the loop condition
DECLARE @ContinueDigging INT
SET @ContinueDigging = 0
--==== Verify if start node exists
SELECT @ContinueDigging = 1 FROM #STD WHERE SearchTerm = @TreeStart
--==== Insert start node in dependency table
INSERT INTO #Dependency
SELECT @TreeStart
--==== As long as we have new inserted search term
WHILE @ContinueDigging <> 0 BEGIN
--==== Insert the dependencies for all search term in #Dependency
INSERT INTO #Dependency
SELECT DISTINCT #STD.Dependency
FROM #STD
INNER JOIN #Dependency
ON #STD.SearchTerm = #Dependency.SearchTerm
--==== Dont insert a record that's already there
AND #STD.Dependency NOT IN (SELECT SearchTerm FROM #Dependency)
--==== Verify if we have inserted new rows
SELECT @ContinueDigging = @@RowCount
END
--==== Remove TreeStart as per requirement
DELETE FROM #Dependency WHERE SearchTerm = @TreeStart
--==== Review content
SELECT * FROM #Dependency ORDER BY SearchTerm
March 22, 2009 at 11:01 pm
You might find the article http://www.sqlservercentral.com/articles/Database+Design/66071/ useful - but you'll have to modify it to use your table.
March 25, 2009 at 8:48 am
Good Evening.....
Thanks @Maxim......
I just ran your solution and it gave the required result..!!
Though I thought I should also develop code on my own.
Can you comment on the code written by me? Its not efficient as yours.
SEARCH_DEPEN is a table containing all dependency as stated in my first post.
/*
EXEC DBO.SP_DEPENDENCY 'B4'
*/
ALTER PROCEDURE [DBO].[SP_DEPENDENCY]
(
@USER_SEARCH CHAR(2)
)
AS
BEGIN
-- Final result table variable
DECLARE @ALL_SEARCH TABLE(ALL_SEARCH CHAR(2))
-- Current search result
DECLARE @TEMP_ALL_CURRENT_SEARCH TABLE(TEMP_ALL_CURRENT_SEARCH CHAR(2))
-- search term to be used in next search (excludes values that already searched)
DECLARE @NEW_SEARCH TABLE(NEW_SEARCH CHAR(2))
INSERT INTO @ALL_SEARCH VALUES(@USER_SEARCH)
INSERT INTO @NEW_SEARCH VALUES(@USER_SEARCH)
-- USED TO IDENTIFY WHETHER SEARCH TERM IS FROM COLUMNA OR COLUMNB
DECLARE @SRC_COL CHAR(1)
IF(@USER_SEARCH LIKE 'A%')
BEGIN
SET @SRC_COL = 'A'
END
ELSE
BEGIN
SET @src_col = 'B'
END
-- Count variable used as search condition for further search
DECLARE @CNT INT
SET @CNT = 1
WHILE @CNT <> 0
BEGIN
IF(@SRC_COL = 'A') BEGIN
INSERT INTO @TEMP_ALL_CURRENT_SEARCH
SELECT DISTINCT COLUMNB FROM SEARCH_DEPEN WHERE COLUMNA IN (SELECT COLUMNA FROM SEARCH_DEPEN D,@NEW_SEARCH N
WHERE D.COLUMNA = N.NEW_SEARCH )
DELETE FROM @NEW_SEARCH
INSERT INTO @NEW_SEARCH
SELECT TEMP_ALL_CURRENT_SEARCH FROM @TEMP_ALL_CURRENT_SEARCH WHERE TEMP_ALL_CURRENT_SEARCH NOT IN (SELECT ALL_SEARCH FROM @ALL_SEARCH )
SELECT @CNT = @@ROWCOUNT
INSERT INTO @ALL_SEARCH
SELECT * FROM @TEMP_ALL_CURRENT_SEARCH
DELETE FROM @TEMP_ALL_CURRENT_SEARCH
SET @SRC_COL = 'B'
END
ELSE
BEGIN
INSERT INTO @TEMP_ALL_CURRENT_SEARCH
SELECT DISTINCT COLUMNA FROM SEARCH_DEPEN WHERE COLUMNB IN (SELECT COLUMNB FROM SEARCH_DEPEN D,@NEW_SEARCH N
WHERE D.COLUMNB = N.NEW_SEARCH )
DELETE FROM @NEW_SEARCH
INSERT INTO @NEW_SEARCH
SELECT TEMP_ALL_CURRENT_SEARCH FROM @TEMP_ALL_CURRENT_SEARCH WHERE TEMP_ALL_CURRENT_SEARCH NOT IN (SELECT ALL_SEARCH FROM @ALL_SEARCH )
SELECT @CNT = @@ROWCOUNT
INSERT INTO @ALL_SEARCH
SELECT * FROM @TEMP_ALL_CURRENT_SEARCH
DELETE FROM @TEMP_ALL_CURRENT_SEARCH
SET @SRC_COL = 'A'
END
END
SELECT DISTINCT ALL_SEARCH FROM @ALL_SEARCH WHERE ALL_SEARCH <> @USER_SEARCH ORDER BY ALL_SEARCH
END
March 25, 2009 at 8:56 am
Please ignore this post
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply