big sql self join

I have a table i need to join on itself.
the table def:
DivisionId,AssemblyNumber,SequenceNumber,ComponentPartNumber,ShipItem
all are varchar except shipitem which is a bit

basically assembly number is a partnumber and component partnumbers are partnumbers.  you add component parts under assemblynumbers
ex
assemblynumber:1
sequencenumber:1
compententpart:A
shipitem:      0
assemblynumber:1
sequencenumber:2
compententpart:B
shipitem:      0
assemblynumber:1
sequencenumber:3
compententpart:C
shipitem:      0
etc... you can also have assemblies under assemblies
assemblynumber:2
sequencenumber:1
compententpart:A
shipitem:      0
assemblynumber:2
sequencenumber:2
compententpart:1 'this the the assemble above
shipitem:      0
now your assembly 2 has a part a and a part 1 that happens to be an assembly composed of other parts.  I need a self join that will give me the records that have a ship item of 1 that starts with the main assembly and then gets all below it.  the layers could go very deep also. ex you have assembly 1 which as assembly 2 which has assembly 3 and 4 which then have more etc. if possible i would also need it to stop on the main assemblies if they are ship items.  ex assembly 1 as a part a under it and assembly 2 under it.  assembly 2 is marked as a shipitem.  it also has parts under it, but i don't care since it is marked already. any ideas?
LVL 3
jrspanoAsked:
Who is Participating?
 
nigelrivettConnect With a Mentor Commented:
This looks like a basic tree structure and you can probably use code that gets a tree hierarchy.

see http://www.experts-exchange.com/jsp/qShow.jsp?ta=dbgen&qid=20136561
for some code I posted earlier to get the full hierarchy.
0
 
acampomaCommented:
listening
0
 
jrspanoAuthor Commented:
is there any way to do it with out a temp table, with joins or something like that?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Unfortunately, SQL Server does not give you this option, while ORACLE does :-(
0
 
nigelrivettCommented:
Only if you want to limit the number of levels - you basically need one join per level.
0
 
jrspanoAuthor Commented:
ok thanks i'll use the temp table idea here is the code from the other post if someone else should buy this question they can see it

by nigelrivett:
Your relationship structure should be maintained by a table with

parent child

so you would have

0  01
0  02
0  03
0  04
01 011
01 012

For this structure a query that will get the entire structure is (for sql server - can be adapted for
others).
From an answer I have given previously. You can adapt this to give your solution.


General way to handle trees. Fails when the squence varchar length is exceeded - in this case 100 levels.

Problems for more than 800.

create table #z (id int, name varchar(20), threadID int null)
insert #z select   1,  'foo', null
insert #z select     2,  'foo2',   null
insert #z select     3,  'foo2a',      2
insert #z select     4,  'foo2b',      2
insert #z select     5,  'foo3',   null
insert #z select     6,  'foo3b',      5
insert #z select     7,  'foo3ba',     6
insert #z select     8,  'foo4',   null
insert #z select     9,  'fooa',       1
insert #z select    10,  'foo2ba',     4

drop table #a
go
create table #a (id int, sequence varchar(1000), levelNo int)
insert #a select id, right(space(10) + convert(varchar(10),id),10), 1 from #z where threadid is null
declare @i int
select @i = 0
while @@rowcount > 0
begin
select @i = @i + 1
insert #a
select #z.id, sequence + right(space(10) + convert(varchar(10),#z.id),10), @i + 1
from #z, #a
where #a.levelNo = @i
and #z.threadID = #a.id
end

select #z.name
from #a, #z
where #a.id = #z.id
order by sequence
0
All Courses

From novice to tech pro — start learning today.