How do I retrieve nested data from a mapping table in SQL?

Question

0.00/5 (No votes)

See more:

Hi,
I have a mapping table in sql with columns Col1 and Col2.The criteria for retrieving the data is Col1 value. For eg: if the Col1 value =6, I should get all the related Col2 data from the table.
6 is mapped to the col2 data 16,20,2,23.Now all these values should be in turn looked up and all the corresponding mappings should be retrieved.
So For 16, it has mapping to 13,15,8 in Col1.Now these values should be looked up and its mappings should be retrieved.
So for Col1 which has value 6, I need to get all the nested values from Col2

Col1 Col2
6 16
6 20
6 2
6 23
8 24
8 4
8 21
8 2
8 16
8 1
13 21
13 22
13 23
13 16
13 2
13 18
15 2
15 16
15 24
15 4
16 35

What I have tried:

I tried CTE in sql server but could not find a solution.

Posted 7-Sep-17 5:59am

Member 3521466

Updated 7-Sep-17 17:43pm

Add a Solution

Comments

David_Wimbley 7-Sep-17 13:42pm

Are you able to change your schema? If i understand you correctly you are trying to create a many to many relationship in the same table which gets funky. If not.

Do you need to get the forward/backward relationship of all columns in 1 query by a selected Id? Because if you need to get all relationships it would just be a SELECT * FROM <table> i imagine.

Member 3521466 7-Sep-17 14:09pm

Thanks David for your reply. I am trying to find the many to many relationship in the same table. For e.g.: if the input is Col1=6 I should get the output as col2 values 1,2,4,16,18,20,21,22,23,24(I did this manually by checking the relationships.)
If the input is col1=16 then the output should be col2 value 35.
Can you please advise?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Answer 1 · 2017-09-07T09:12:00

So i think you aught to change your schema so this query isn't the most ideal way of going about this im sure. I can think of a way of doing this while looping over each row and storing the results which would be slower than this also.

Basically it uses a join and a sub query to get the column1 + column2 matching results.

I get the same results as OP's original question but I don't get the same results as specified in the comment to my question. Manually doing the relationship i don't see how 18 can be a possible match for example.

SQL

DECLARE @Relationship TABLE (
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Column1 INT NULL,
	Column2 INT NULL
);

INSERT INTO @Relationship ( Column1, Column2 ) VALUES (6,16);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (6,20);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (6,2);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (6,23);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (8,24);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (8,4);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (8,21);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (8,2);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (8,16);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (8,1);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (13,21);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (13,22);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (13,23);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (13,16);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (13,2);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (13,18);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (15,2);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (15,16);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (15,24);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (15,4);
INSERT INTO @Relationship ( Column1, Column2 ) VALUES (16,35);


DECLARE @Input INT = 6

SELECT DISTINCT A.Column2 AS Result1 FROM @Relationship AS A
LEFT OUTER JOIN @Relationship AS B ON B.Column1 = A.Column2
WHERE A.Column1 = @Input 

UNION ALL

SELECT DISTINCT  A.Column1 AS Result1 FROM @Relationship AS A 
WHERE A.Column2 IN (SELECT DISTINCT A.Column2 FROM @Relationship AS A
LEFT OUTER JOIN @Relationship AS B ON B.Column1 = A.Column2
WHERE A.Column1 = @Input ) AND A.Column1 <> @Input

The above gives me the output of

Quote:
2
16
20
23
8
13
15

Patrice T · Answer 2 · 2017-09-07T17:43:00

Solution 2

Have a look there
SQL queries to manage hierarchical or parent-child relational rows in SQL Server[^]
Managing Hierarchical Data in MySQL — Mike Hillyer's Personal Webspace[^]
Hierarchical Queries[^]

Posted 7-Sep-17 17:43pm

Patrice T

Updated 7-Sep-17 18:00pm

v2