How to Loop the unmatched rows data from two tables in sqlserver 2012

• saikumar.mca2007

  SSC-Addicted

  Points: 438

  More actions

  January 12, 2015 at 9:45 pm

  #313624

  Hi All,

  I need solution on below issue which became painful in my current project ,

  Please suggest me on below issue

  Issue: we are having folder table and team table as like below structure.

  Folderlist (F)Table: (

  ==============

  id ,folder_name, parent_id

  1, c, 101

  2,b,202

  3,c,203

  Teamlist table (T)

  ============

  team_id, Team_name, Parent_folderid

  101 , mobile,101

  202 ,Tab,200

  200, Phone,200

  203,apple,205

  205,nokia,208

  208,samsung,208

  If F.parent_id(101)=T.team_id(101) and T.team_id(101)=T.parent_folder_id (101)

  then output should come as 'Mobile/c' (this is for f.parent_id=101)

  If F.Parent_id=T.team_id and T.team_id!=T.parent_folder_id

  then parent_folder_id have to start search on team_id column where it got match and pick the Team_name from that corresponding id

  Ex: F.parent_id=202 is matching with T.Team_id (202) but this T.team_id(202) is not matching with T.parent_folderid(200) , so this T.parent_folderid (200) have to search on T.id (200) ,if now T.id(200) is matching with T.Parent_folder_id(200) then it have to give the names from the starting hirache

  like phone/tab/b (this is for F.parent_id=202)

  Kindly help me on this.

• Brandie Tarvin

  SSC Guru

  Points: 173068

  More actions

  January 13, 2015 at 6:37 am

  #1769785

  Can you try explaining in more English and less code what you're trying to accomplish?

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 13, 2015 at 8:13 am

  #1769830

  See if this helps

  CREATE TABLE #Folderlist(id INT, folder_name VARCHAR(10),parent_id INT)

  INSERT INTO #Folderlist(id ,folder_name, parent_id)

  SELECT 1, 'c', 101 UNION ALL

  SELECT 2,'b',202 UNION ALL

  SELECT 3,'c',203;

  CREATE TABLE #Teamlist(team_id INT, Team_name VARCHAR(10),Parent_folderid INT)

  INSERT INTO #Teamlist(team_id, Team_name, Parent_folderid)

  SELECT 101 , 'mobile',101 UNION ALL

  SELECT 200, 'Phone',200 UNION ALL

  SELECT 202 ,'Tab',200 UNION ALL

  SELECT 203,'apple',205 UNION ALL

  SELECT 205,'nokia',208 UNION ALL

  SELECT 208,'samsung',208;

  WITH Recur AS (

  SELECT team_id, Team_name, Parent_folderid, CAST(Team_name AS VARCHAR(1000)) AS Full_Team_name

  FROM #Teamlist

  WHERE team_id = Parent_folderid

  UNION ALL

  SELECT t.team_id, t.Team_name, t.Parent_folderid, CAST(r.Full_Team_name + '/' + T.Team_name AS VARCHAR(1000))

  FROM #Teamlist t

  INNER JOIN Recur r ON r.team_id = t.Parent_folderid

  WHERE t.team_id <> t.Parent_folderid)

  SELECT r.Full_Team_name + '/' + f.folder_name AS folder_name

  FROM Recur r

  INNER JOIN #Folderlist f ON f.parent_id = r.team_id;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• saikumar.mca2007

  SSC-Addicted

  Points: 438

  More actions

  January 19, 2015 at 7:35 am

  #1771113

  Thanks A Lot, its working ..:)