Join

Question

Post reply

Join

Tracey Hein

Valued Member

Points: 63
More actions
March 14, 2006 at 11:26 am

#93665

I have 2 tables
Table 1 looks like
Task
----------
Task C
Task D
Task A
Task B

Table 2 looks like
Table 2

Task                Prerequisite Task
-------------------------------------------
Task B            Task A
Task C            Task A
Task D            Task A
Task D            Task B
Task D            Task C
we need to put tasks in order they can be worked on, i.e. it should be Task A, Tasks B and C in any order, Task D.

Collect result in Output Table
Step 1:
   Get all tasks that have no prerequisites and insert them into Output Table (got this part)

Step 2:
    Get tasks where ALL their prerequisites are in Output Table and insert them into Output Table.  Continue looping until done.

I'm having problem with Step 2: don't know how to say ALL.
I have tried all types of joins and cant seem to get the output to be what is expected

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

JeffB Hall of Fame Points: 3541 More actions · Answer 1

Try something like this. It may be possible to enhance it to not use a loop.

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

declare @Table1 table (task varchar(6))

insert @Table1 values ('Task C')

insert @Table1 values ('Task D')

insert @Table1 values ('Task A')

insert @Table1 values ('Task B')

declare @Table2 table (task varchar(6), pretask varchar(6))

insert @Table2 values ('Task B', 'Task A')

insert @Table2 values ('Task C', 'Task A')

insert @Table2 values ('Task D', 'Task A')

insert @Table2 values ('Task D', 'Task B')

insert @Table2 values ('Task D', 'Task C')

declare @orderid int

set @orderid = 1

declare @Order table (orderid int, task varchar(6))

insert @Order (orderid, task)

select @orderid, t1.task

from @Table1 t1

left outer join @Table2 t2

on t1.task = t2.task

where t2.task is null

while exists (select * from @Table1 t1 left outer join @order o on t1.task = o.task where o.task is null)

begin

set @orderid = @orderid + 1

insert @Order (orderid, task)

select distinct @orderid, t2.task

from @table2 t2

left outer join @order o

on t2.task = o.task

left outer join (select distinct t2.task

from @table2 t2

left outer join @order o

on t2.pretask = o.task

where o.task is null) x

on t2.task = x.task

where o.task is null

and x.task is null

end

select * from @order

Tracey Hein Valued Member Points: 63 More actions · Answer 2

Thank you so much. I never tried the join within the join before. I am sure I will use this over and over again now that you have shown me the light

thank you

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Hi Tracey, Jeff

Just for fun, here's a different way (although similar, I admit)

I think it's not possible without a loop if the depth is unrestricted

--This SQL script is safe to run

declare @Table1 table (task varchar(6))

insert @Table1

select 'Task C'

union select 'Task D'

union select 'Task A'

union select 'Task B'

declare @Table2 table (task varchar(6), pretask varchar(6))

insert @Table2

select 'Task B', 'Task A'

union select 'Task C', 'Task A'

union select 'Task D', 'Task A'

union select 'Task D', 'Task B'

union select 'Task D', 'Task C'

declare @Order table (orderid int, task varchar(6))

insert into @Order

select 1, task from @Table1 where task not in (select task from @Table2)

declare @orderid int

set @orderid = 2

while 0 = 0

begin

insert into @Order

select @orderid, t2.task

from @Table2 t2 left outer join @Order o on t2.pretask = o.task

where t2.task not in (select task from @Order)

group by t2.task

having count(t2.task) = count(o.task)

if @@rowcount = 0 break

set @orderid = @orderid + 1

end

select * from @Order

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.