Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-03-02 : 08:46:50
|
| Mel writes "Creating SQL for the following problem has so far eluded me. Can you help?I have a table containing tasks, e.g.1 Install pump2 Remove pump3 Install valve4 Remove valve5 Overhall engine6 Assemble engineThe training supervisor states that if a staff member is trained to do a certain task, then that training may also be sufficient for other tasks. I therefore have a table with 'explicit' training equivalents, e.gTask Equivalent1 32 42 3I want SQL to produce the results of all equivalents. I.e, if 1 = 3, then 3 = 1, if 6 = 1 and 1 = 3 then 6 = 3. So from the above, I want to generate:1 33 12 44 22 33 2 (These are the easy ones!)1 2 (Implied relations :)2 13 44 3In this example there are only a few implied results, but there could potentially be many, causing multiple recursion.Can you help?" |
|
|
gurpleman
Starting Member
2 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-02 : 14:34:21
|
| [code]-- prepare sample datadeclare @t table (task int, equivalent int)insert @tselect 1, 3 union allselect 2, 4 union allselect 2, 3-- stage datadeclare @s table (t1 int, t2 int)insert @sselect task, equivalent from @tunionselect equivalent, task from @twhile @@rowcount > 0 insert @s select o.t2, e.t1 from @s as o inner join @s as e on e.t1 = o.t2 left join @s as s on s.t1 = o.t2 and s.t2 = e.t1 where s.t1 is null-- show the expected outputselect distinct * from @s order by t1, t2[/code]Peter LarssonHelsingborg, Sweden |
 |
|
|
|
|
|
|
|
Recursion SQL