Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

recursive query from multiple tables Expand / Collapse
Author
Message
Posted Monday, October 12, 2009 9:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 4, 2011 11:04 AM
Points: 13, Visits: 69
Ok you DBA Gods this one has got me to that point that I just want to say F-it I cant do it I give up.

I hope I can explain this well...

I need to create a recursive query to supply the data to a TELERIK treeview. The problem is that the data I have to play with is not in an easy format to work with.

I have three tables. WORK_ORDER, PART AND REQUIREMENT (Database is from Infor ERP VISUAL - ERP manufacturing Software)


The WORK_ORDER table can have parts and the REQUIREMENT table can also have parts. Both connect to the same part table using the Part_id. A work order can have multiple requirements with many parts. Here is the view that gives me all the data I need.

SELECT WORK_ORDER.BASE_ID, WORK_ORDER.PART_ID AS WO_PART_ID, PART_1.DESCRIPTION AS WO_DESCRIPTION, REQUIREMENT.PART_ID,
PART.DESCRIPTION
FROM WORK_ORDER INNER JOIN
PART AS PART_1 ON WORK_ORDER.PART_ID = PART_1.ID INNER JOIN
REQUIREMENT INNER JOIN
PART ON REQUIREMENT.PART_ID = PART.ID ON WORK_ORDER.BASE_ID = REQUIREMENT.WORKORDER_BASE_ID
WHERE (REQUIREMENT.STATUS = 'R')

However, I want to Traverse this data so I can create a parent child relationship or change the above query to a recursive query. NOTE almost all parents are duplicated because they also can have multiple parts and requirements.

BASE_ID WO_PART_ID WO_DESCRIPTION PART_ID DESCRIPTION
04060 30766 Truss Rod Gusset - tall 01034 Flat Bar 1/4 x 5 per inch
04061 30767 Truss Rod Gusset - short 01034 Flat Bar 1/4 x 5 per inch
04109 30420 Impeller 01037 Flat Bar 1/8 X 1 per inch
03337 28604 Gusset 01047 Round, 1/2 HR per inch
03582 28604 Gusset 01047 Round, 1/2 HR per inch
Post #801930
Posted Tuesday, October 13, 2009 2:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:57 AM
Points: 1,949, Visits: 8,304
Is this a bill of materials ?

Try this...
http://sqlblog.com/blogs/john_paul_cook/archive/2009/10/11/bill-of-materials-where-used-query.aspx




Clear Sky SQL
My Blog
Kent user group
Post #802007
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse