Looking for ideas on how to build a query

• bob mazzo

  SSCommitted

  Points: 1684

  More actions

  September 23, 2007 at 8:14 pm

  #121711

  My order database may have linked orders, and I'm trying to write a query to find all  linked orders.

  For example I have the following parent order number :

  848313

  Now my order numbers look like this in the table:

  Ord_num   Parent_Num

  848313      0

  848314      848313

  848315      848314

   

  Now when I have 848313 in memory, I need a query to retrieve both 848314 and 315 as well.

  Of course the following Select only retrieves order 848314 :

     select a.ticket from order_fx a where a.parent_num = 848313

  but how to I also retieve order 848315, which is part of this total order strategy (as the client refers to it).

   

  Thank you very much

  Bob

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  September 24, 2007 at 5:09 am

  #735562

  Here's one way to do it by a selfjoin.

  There's a drawback, though, in that using a function in the join will invalidate indexusage,

  so there may not be the best performance on large volumes.

  create

  table #x ( ord_num int not null, parent_num int not null, ord_txt varchar(15) not null )

  insert

  #x

  select 848313, 0, 'order 848313' union all

  select 848314, 848313, 'order 848314' union all

  select 848315, 0848314, 'order 848315'

   

   

  select b.ord_num, b.parent_num, b.ord_txt

  from   #x a

  join   #x b

  on     coalesce(nullif(b.parent_num, 0), b.ord_num) = a.ord_num

  ord_num     parent_num  ord_txt

  ----------- ----------- ---------------

  848313      0           order 848313

  848314      848313      order 848314

  848315      848314      order 848315

  (

  3 row(s) affected)

   

  /Kenneth