Querying the same column twice?
I have a table where I need to pull all the component parts for a given part number. In the fictious data below, the information I need to be able to pull back is all the components for the part number 4444ZZZ-01.
Table Name = Product Structure
PARPRT_02 COMPRT_02
4444ZZZ-01 102441
4444ZZZ-01 102442
4444ZZZ-01 801277
801277 101483
801277 D801277
I know how I can pull the general information
Code Snippet
Select PARPRT_02, COMPPRT_02
FROM [Product Structure]
WHERE PARPRT_02='4444ZZZ-01'
But what I am having trouble with is stating IF the COMPRT_02 is also listed in PARPRT_02 then show this information also. I have tried to research all kinds of joins(inner, self, right, left), unions, derived tables....but most of the explanations I have found only go so far and I have found nothing that really nails it.
Thanks
(this table basically is a Build of materials where a given model has multiple parts and any given part might have
sub parts listed under them) example 4444ZZZ-01 is a computer with a part 801277 that is a wiring assembly. The part 801277 has two parts associated with it - 101483 - the wiring assembly itself and D801277 a document file.
[1371 byte] By [
ssimon] at [2008-1-9]
hi,
you are actually looking for a recursive solution to a "Bill of Material" problem.. that's to say you need the explosion of, say, a component, into each successive component it is built with, down to the first one..
SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.[Product Structure] ( PARPRT_02 varchar(10), COMPRT_02 varchar(10) ); INSERT INTO dbo.[Product Structure] VALUES ( '4444ZZZ-01', '102441'); INSERT INTO dbo.[Product Structure] VALUES ( '4444ZZZ-01', '102442'); INSERT INTO dbo.[Product Structure] VALUES ( '4444ZZZ-01', '801277'); INSERT INTO dbo.[Product Structure] VALUES ( '801277', '101483'); INSERT INTO dbo.[Product Structure] VALUES ( '801277', 'D801277'); INSERT INTO dbo.[Product Structure] VALUES ( '101483', 'added1'); INSERT INTO dbo.[Product Structure] VALUES ( 'added1', 'added2'); INSERT INTO dbo.[Product Structure] VALUES ( 'added2', 'added3'); GO DECLARE @part varchar(10); SET @part = '4444ZZZ-01'; WITH CTE AS ( SELECT ps.PARPRT_02, ps.COMPRT_02, 1 AS [Level] FROM dbo.[Product Structure] ps WHERE PARPRT_02 = @part UNION ALL SELECT ps.PARPRT_02, ps.COMPRT_02, c.Level + 1 FROM CTE c JOIN dbo.[Product Structure] ps ON c.COMPRT_02 = ps.PARPRT_02 ) SELECT CONVERT( char(10), REPLICATE('|', c.Level) ) AS [Path], c.PARPRT_02, c.COMPRT_02, c.[Level] FROM CTE c; GO DROP TABLE dbo.[Product Structure]; --< Path PARPRT_02 COMPRT_02 Level - - - -- | 4444ZZZ-01 102441 1 | 4444ZZZ-01 102442 1 | 4444ZZZ-01 801277 1 || 801277 101483 2 || 801277 D801277 2 ||| 101483 added1 3 |||| added1 added2 4 ||||| added2 added3 5
you can get that result via recursion provided into the box by SQL Server 2005 via Common Table Expressions (you can look for in BOL)
regards
Is there a simpler measure? I am using this in a web form and connecting to the database via a connection string with in-line code. I am not sure that I can have it build out a temp database since one or more customers may hit the search page at a time. (Thanks for the information though - I have started reading up on recursive now - still learning from the ground up)
edit: Just to add some info - I only need to go down one level and the only items I need to expand are LIKE '8%'
hi,
 ssimon wrote: |
| Is there a simpler measure? I am using this in a web form and connecting to the database via a connection string with in-line code. I am not sure that I can have it build out a temp database since one or more customers may hit the search page at a time. (Thanks for the information though - I have started reading up on recursive now - still learning from the ground up) edit: Just to add some info - I only need to go down one level and the only items I need to expand are LIKE '8%' | |
tempdb is just used in this sample in order not to mess other databases.. you can (and actually should) do that in your actual working database context.. it's just a way to provide a clean sample ..
at server side, this is one possible solution.. other are based on nested set theory or materialized paths and the like.. but the recursion possibility provided by SQL Server 2005 is indeed very functional and easy..
an interesting lecture about trees and hierarchies is this book by Joe Celko, but I do personally prefer materialized paths approach as Itzik Ben Gan suggest in his article and books ..
the fact you only need to go down 1 level only is indeed irrilevant, as you have to follow the very same pattern, or, "hardcode" a semijoin, similar to
SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.[Product Structure] ( PARPRT_02 varchar(10), COMPRT_02 varchar(10) ); INSERT INTO dbo.[Product Structure] VALUES ( '4444ZZZ-01', '102441'); INSERT INTO dbo.[Product Structure] VALUES ( '4444ZZZ-01', '102442'); INSERT INTO dbo.[Product Structure] VALUES ( '4444ZZZ-01', '801277'); INSERT INTO dbo.[Product Structure] VALUES ( '801277', '101483'); INSERT INTO dbo.[Product Structure] VALUES ( '801277', 'D801277'); INSERT INTO dbo.[Product Structure] VALUES ( '101483', 'added1'); INSERT INTO dbo.[Product Structure] VALUES ( 'added1', 'added2'); INSERT INTO dbo.[Product Structure] VALUES ( 'added2', 'added3'); GO DECLARE @part varchar(10); SET @part = '4444ZZZ-01'; SELECT ps.PARPRT_02, ps.COMPRT_02, ISNULL(ps2.PARPRT_02, 'n.a.') AS [Child PARPRT_02], ISNULL(ps2.COMPRT_02, 'n.a.') AS [Child COMPRT_02] FROM dbo.[Product Structure] ps LEFT JOIN dbo.[Product Structure] ps2 ON ps2.PARPRT_02 = ps.COMPRT_02 WHERE ps.PARPRT_02 = @part ORDER BY CASE WHEN ps2.PARPRT_02 IS NULL THEN 1 ELSE 0 END, ps.PARPRT_02, ps.COMPRT_02; GO DROP TABLE dbo.[Product Structure]; --<- PARPRT_02 COMPRT_02 Child PARPRT_02 Child COMPRT_02 - - 4444ZZZ-01 801277 801277 101483 4444ZZZ-01 801277 801277 D801277 4444ZZZ-01 102441 n.a. n.a. 4444ZZZ-01 102442 n.a. n.a.
where you hardcode just 1 subnode search via the join condition .. as you can see, there's no way to follow the path down to have 'added1' and 'added2' elements returned...
regards
