brian

I have been beating my head over this query. I would
appreciate some help.

I am using ordnum (order number) and orlink (orders that
link to another)

Main Order Link To Order
76213
72193 76213
85613 76213
42910 76213

76213 is the main order and the other 3 below it are
errors that link to the original order. I can write a
query to select all orders that link to the original. I
need help writing a query to get the following
information. All orders that link to '85613' If a user
types in '85613' the results should be:

'76213'
'72193'
'42910'

Thanks for any help

i_am_don_and_you?

Here is the solution:

create table xorders
(
main_order varchar(30),
link_to_order varchar(30)
)

insert into xorders values (76213, null)
insert into xorders values (72193, 76213)
insert into xorders values (85613, 76213)
insert into xorders values (42910, 76213)

select * from xorders

select main_order from xorders
where link_to_order = (select link_to_order from xorders where main_order =
'85613')
and main_order <> '85613'
or main_order = (select link_to_order from xorders where main_order =
'85613')

******************************

You can create a stored procedure for this as:

create procedure xprocedure
@x varchar(20)
as
select main_order from xorders
where link_to_order = (select link_to_order from xorders where main_order =
@x)
and main_order <> @x
or main_order = (select link_to_order from xorders where main_order = @x)

--and you can execute it as

execute xprocedure '85613'

vishal parkar

Brian,

Refer to following two solutions, that may help you.

--sample table and records.
create table #t
(
order_nm varchar(30),
parent_ord varchar(30)
)

insert into #t values (76213, null)
insert into #t values (72193, 76213)
insert into #t values (85613, 76213)
insert into #t values (42910, 76213)
insert into #t values (76214, null)
insert into #t values (72194, 76214)
insert into #t values (85614, 76214)
insert into #t values (42912, 76214)

--see following 2 queries.

declare @ord varchar(30)
set @ord = '76213'

select a.order_nm , a.parent_ord
from #t a
where exists(
select * from #t b where
coalesce(b.parent_ord, b.order_nm)
= (coalesce(a.parent_ord, a.order_nm))
and b.order_nm = @ord)
and a.order_nm <> @ord


--OR

select a.order_nm , a.parent_ord
from #t a,
(select * from #t where
order_nm = @ord)b
where a.order_nm <> b.order_nm
and coalesce(a.parent_ord, a.order_nm) =
coalesce(b.parent_ord, b.order_nm)

--
-Vishal