Cannot query full scope of many to many relationship of a single table

Question

I have a WorksOrder table where specific members link to other WorksOrders in the same table.

To do this I have created a linking table that links WorksOrders to another WorksOrder and has the columns WorksOrderAID and WorksOrderBID.

Everything has been working fine so far, however when I need to query a certain works order only the ones with direct links are returned.

This is a problem as they are all supposed to be on the same level and do not follow a parent/child relationship.

If there are WorksOrders 0, 1, 2, 3 and 4 and all are linked directly to 0.

When I query

SELECT WorksOrderBID from LinkedWorksOrders where WorksOrderAID = @WorksOrderID
UNION ALL
SELECT WorksOrderAID from LinkedWorksOrders where WorksOrderBID = @WorksOrderID

And @WorksOrderID is WorksOrder 0, I receive get back all the WorksOrders which is good.

If, however @WorksOrderID is 1 then I only retrieve 0, rather than all of them. This would also come up as a problem if 1, 2, 3 and 4 had links to other works orders that aren't directly linked to 0.

I am having a difficult time working on a stored procedure to return all the related works orders and I am strongly suspecting that the design has gone awry at some point but I just can't find it.

you need a tree query: http://stackoverflow.com/questions/5522478/sql-query-for-tree-table or here: http://stackoverflow.com/questions/5034153/sql-server-tree-query — deterministicFail, Nov 21 '13 at 14:19
the reason for this is you have linked 0 to 0 1 2 3 4, but 1 is only linked to 0. for your expected result you would need to insert links between all of them. — Brett Schneider, Nov 21 '13 at 14:22

Brett Schneider · Accepted Answer · 2013-11-21T14:54:16.263

try inserting the missing links between the other WorksOrderID:

insert into LinkedWorksOrders (WorksOrderAID, WorksOrderBID)
values (1,1);

insert into LinkedWorksOrders (WorksOrderAID, WorksOrderBID)
values (1,2);

insert into LinkedWorksOrders (WorksOrderAID, WorksOrderBID)
values (1,3);

insert into LinkedWorksOrders (WorksOrderAID, WorksOrderBID)
values (1,4);

insert into LinkedWorksOrders (WorksOrderAID, WorksOrderBID)
values (2,2);

insert into LinkedWorksOrders (WorksOrderAID, WorksOrderBID)
values (2,3);

insert into LinkedWorksOrders (WorksOrderAID, WorksOrderBID)
values (2,4);

insert into LinkedWorksOrders (WorksOrderAID, WorksOrderBID)
values (3,3);

insert into LinkedWorksOrders (WorksOrderAID, WorksOrderBID)
values (3,4);

insert into LinkedWorksOrders (WorksOrderAID, WorksOrderBID)
values (4,4);

EDIT

alternatively, you could self-join your linkedworksorders to achieve more depth in linking your ids, however this is not exhaustive:

SELECT w1.WorksOrderAID 
  from LinkedWorksOrders w1
 inner join
       LinkedWorksOrders w2
    on w1.WorksOrderBID = w2.WorksOrderBID
 where w2.WorksOrderAID = @WorksOrderID
UNION
SELECT w1.WorksOrderBID 
  from LinkedWorksOrders w1
 inner join
       LinkedWorksOrders w2
    on w1.WorksOrderAID = w2.WorksOrderAID
 where w2.WorksOrderBID = @WorksOrderID

if you have worksorder 1 linked to worksorder 2 and worksorder 2 linked to 3, then querying for 1 will not yield 3. however if you have 1 linked to 2 and 3, it will give 2 when you query 3. hope it helps

Rather than explicitly creating the links through a large number of links, I was looking for a way to rewrite the stored procedure that would be able to return all the implicit links. Is this unfeasible? — SkollSunman, Nov 21 '13 at 14:34
you could try a self join on the linked table. I will put in an edit. — Brett Schneider, Nov 21 '13 at 14:51

Cannot query full scope of many to many relationship of a single table

1 Answers1