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.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  BOM table query

Author  Topic  steve_o Starting Member 3 Posts Posted - 2005-05-10 : 11:30:43 Hallo, I have a table (Table A) with item numbers (t_pitm) and quantities (qty). The problem is that some items are parent items, and consist of 1 or more children items. I have another table (Table B) that contains the relationship from parent item (t_pitm) to child item (t_sitm), as well as the quantity of children per parent. I am trying to create a view of the 2 tables, whereby the single parent items that are in Table A are returned as their children items from Table B??? I am able to do this programmatically outside the DB, but would like to keep it as a table or view if possible? Any suggestions??? Regards, stephen. Table A: [CODE] t_pitm | t_dsca | kunde | qty -----------+-----------------+--------+----- 091203450 | VERSTELLPUMPE | 801020 | 10 091203450 | VERSTELLPUMPE | 801020 | 5 091203450 | VERSTELLPUMPE | 801020 | 3 091203450 | VERSTELLPUMPE | 801020 | 6 091203450 | VERSTELLPUMPE | 801020 | 3 091203450 | VERSTELLPUMPE | 801020 | 5 091203450 | VERSTELLPUMPE | 801020 | 6 091203450 | VERSTELLPUMPE | 801020 | 6 091203450 | VERSTELLPUMPE | 801020 | 6 091203450 | VERSTELLPUMPE | 801020 | 6 091203450 | VERSTELLPUMPE | 801020 | 6 091203450 | VERSTELLPUMPE | 801020 | 8 091203450 | VERSTELLPUMPE | 801020 | 8 091203450 | VERSTELLPUMPE | 801020 | 4 091203450 | VERSTELLPUMPE | 801020 | 8 091203450 | VERSTELLPUMPE | 801020 | 8 091203450 | VERSTELLPUMPE | 801020 | 5 091203450 | VERSTELLPUMPE | 801020 | 8 091203450 | VERSTELLPUMPE | 801020 | 6 091203450 | VERSTELLPUMPE | 801020 | 6[/CODE] Table B: [CODE] t_pitm | t_sitm | qty ---------+----------+----- 9078760 | 9076801 | 1 9078760 | 9076900 | 1 9078760 | 9077055 | 1 9078760 | 9077971 | 1 9078760 | 9078761 | 1 9080861 | 9079700 | 1 9080861 | 9080860 | 1 9080862 | 10115843 | 1 9080862 | 9072112 | 1 9078053 | 9075306 | 1[/CODE]