CTE Question

• David-155102

  SSCarpal Tunnel

  Points: 4361

  More actions

  November 26, 2008 at 4:17 am

  #196825

  I have created a CTE, which works great, but now I have a problem with duplicates occurring. The script below sets up an example:

  create table #test

  ( id int, line int)

  insert into #test

  select 10031, 83

  union all

  select 10031, 159

  union all

  select 10031, 160

  union all

  select 10031, 161

  union all

  select 10031, 505

  union all

  select 10031, 14001

  union all

  select 10031, 80000392

  union all

  select 80000392, 505

  union all

  select 505, 83

  union all

  select 505, 159

  union all

  select 505, 160

  union all

  select 505, 161

  union all

  select 505, 14001

  union all

  select 505, 505

  ;WITH AllNodes (id, line, node_level)

  AS

  (

  SELECT fr.id, fr.line, 0 AS node_level

  FROM #test fr

  WHERE fr.id = 10031

  UNION ALL

  SELECT fra.id, fra.line, b.node_level + 1

  FROM #test fra

  INNER JOIN AllNodes b ON fra.id = b.line

  WHERE fra.id <> fra.line

  )

  SELECT an.id, an.line, an.node_level

  FROM AllNodes an

  drop table #test

  The problem is being caused by id 505 because it is both an id AND a line at the same time. Does anyone know how I can implement a way of excluding these occurrences?

  Also, is there a way of preventing lines being included if they have already appeared in a previous relationship, for example line 161 is a child of id 10031 and 505, ideally I only want children of 505 that are not already included in the relationship by 10031. Hope that makes sense :crazy:

  Thanks

  David

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  November 26, 2008 at 6:07 am

  #903663

  For the sample data provided, please give your expected results.

  Are they:

  10031832

  100311592

  100311602

  100311612

  800003925051

  10031140012

  10031800003920

  ?

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• David-155102

  SSCarpal Tunnel

  Points: 4361

  More actions

  November 26, 2008 at 7:09 am

  #903697

  Garadin (11/26/2008)

  ---

  For the sample data provided, please give your expected results.

  Are they:

  10031832

  100311592

  100311602

  100311612

  800003925051

  10031140012

  10031800003920

  ?

  Hi,

  Yes, that's exactly what I'd like to return.

  Thanks

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  November 26, 2008 at 8:01 am

  #903761

  Your CTE is fine, all that needs to change is your final select.

  SELECT MAX(an.id) ID, an.line, MAX(an.node_level) Node_Level

  FROM AllNodes an

  GROUP BY an.line

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• David-155102

  SSCarpal Tunnel

  Points: 4361

  More actions

  November 26, 2008 at 8:15 am

  #903777

  Great! Thanks Seth.