#Mindnode insert bracket code#

,ROW_NUMBER() OVER (PARTITION BY a1.Name1, a2.Name1 ORDER BY (SELECT 1)) AS n If the output should contain only two-way relations ('XX' + 'A') AND ('A' + 'XX'), try this: JOIN a2 ON a1.Name1 = a2.Name2 and a1.Name2 = a2.Name1 AND a1.ID < a2.ID If you only want to get those where both fields are fitting the needed query would simply be: SELECT a1.ID AS ID1, a2.ID AS ID2, a1.Name1, a2.Name1 AS Name2 I still use Guid's as ID's and just use Int's here to make it a bit more readable.ĭECLARE TABLE (ID INT, Name1 VARCHAR(50), Name2 VARCHAR(50)) I've pulled my hair all morning but i can't figure this out.

#Mindnode insert bracket code#

When i use this code it sure works fine with the names but it doesn't match the ID's correctly. Order by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end)) as rn Row_Number() over (Partition by CheckSum(Case when a1.Name1>a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end) INSERT INTO VALUES ( 8, 'Hola! Better Internet', 'ZenMate' )ĪND a1.ID a2.Name1 then a2.Name1+a1.Name1 else a1.Name1+a2.Name1 end) ck, - just for display INSERT INTO VALUES ( 7, 'Hola! Better Internet', 'PPLive Video Accelerator' ) INSERT INTO VALUES ( 6, 'ZenMate', 'Hola! Better Internet' ) INSERT INTO VALUES ( 5, 'PPLive Video Accelerator', 'Hola! Better Internet' ) INSERT INTO VALUES ( 4, 'Hitman Pro', 'avast' ) INSERT INTO VALUES ( 3, 'avast', 'Hitman Pro' ) INSERT INTO VALUES ( 2, 'MindNode', 'XMind' ) INSERT INTO VALUES ( 1, 'XMind', 'MindNode' )

DECLARE TABLE (ID INT, Name1 VARCHAR(50), Name2 VARCHAR(50)) The solution doesn't work with more data. It's a given in this view that the relation goes both ways.ĮDIT: Seems like my example was to simple. I want to show that Avast and Hitman has a relation but in this view i do not need to show in what 'direction' they have an relation. Required Output ID1 | ID2 | Name1 | Name2