Stuck on complex reporting problem 1

[claudermilk]

I'm stuck for ideas on how to solve a somewhat complex reporting problem. I have two tables which I'm trying to join; the complex part is the second table I need to join several times. The data looks like this:

Table1
ParentPart
Component
QtyPer

Table2
FormType
KeyField
FieldName
AlphaValue


In table 2 I'm filtering the FormType for appropriate entries. I need to relate KeyField to Component for each of four different values found in FieldName to give me AlphaValue. To complicate things I may get from one to four hits on FieldName for each Component-KeyField pairing. The end record I'm trying to get should be like:

ParentPart, Component, QtyPer, AlphaValue(1), AlphaValue(2), AlphaValue(3), AlphaValue(4)

I've tried subqueries and self-joins and cannot seem to find a way to accomplish this. I can easily get the first table query set up, and I've even gotten Table2 to produce the four fields like I'd like to see, but I cannot for the life of me get the two parts into a single result.

Any help would be appreciated.

 

[r937]

select t1.ParentPart
     , t1.Component
     , t1.QtyPer
     , t2_1.AlphaValue as AlphaValue1
     , t2_2.AlphaValue as AlphaValue2
     , t2_3.AlphaValue as AlphaValue3
     , t2_4.AlphaValue as AlphaValue4
  from Table1 as t1
left outer
  join Table2 as t2_1
    on t1.Component = t2_1.KeyField 
   and t2_1.FieldName = 'Curly'
left outer
  join Table2 as t2_2
    on t1.Component = t2_2.KeyField 
   and t2_2.FieldName = 'Larry'
left outer
  join Table2 as t2_3
    on t1.Component = t2_3.KeyField 
   and t2_3.FieldName = 'Moe'
left outer
  join Table2 as t2_4
    on t1.Component = t2_4.KeyField 
   and t2_4.FieldName = 'Shemp'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[dnoeth]

another way:

select t1.ParentPart
     , t1.Component
     , t1.QtyPer
     , max(case when t2.FieldName = 'Curly' 
                then t2.AlphaValue as AlphaValue1 end) as AlphaValue1
     , max(case when t2.FieldName = 'Larry'
                then t2.AlphaValue as AlphaValue1 end) as AlphaValue2
     , max(case when t2.FieldName = 'Moe'
                then t2.AlphaValue as AlphaValue1 end) as AlphaValue3
     , max(case when t2.FieldName = 'Shemp'
                then t2.AlphaValue as AlphaValue1 end) as AlphaValue4
from Table1 as t1
join Table2 as t2_1
  on t1.Component = t2_1.KeyField
group by 
  t1.ParentPart
  , t1.Component
  , t1.QtyPer

 

[claudermilk]

Thanks! That first one, while painfully slow on my system, gives me the reults I need. I wasn't sure how to get the one table linked multiple times, and this seems so stupidly simple now that I see it.

 

[claudermilk]

I just tweaked it a bit and ended up with the following code:

SELECT t1.ParentPart, 
t1.Component, 
t1.QtyPer, 
t2_1.AlphaValue as AlphaValue1,
t2_2.AlphaValue as AlphaValue2,
t2_3.AlphaValue as AlphaValue3,
t2_4.AlphaValue as AlphaValue4
FROM dbo.BOMSTRUCTURE as t1
left outer join dbo.ADMFORMDATA as t2_1 on t1.Component = t2_1.KeyField and t2_1.FieldName ="Mfr1" and t2_1.FormType = "STK"
left outer join dbo.ADMFORMDATA as t2_2 on t1.Component = t2_2.KeyField and t2_2.FieldName ="Mfr2" and t2_2.FormType = "STK"
left outer join dbo.ADMFORMDATA as t2_3 on t1.Component = t2_3.KeyField and t2_3.FieldName ="Mfr3" and t2_3.FormType = "STK"
left outer join dbo.ADMFORMDATA as t2_4 on t1.Component = t2_4.KeyField and t2_4.FieldName ="Mfr4" and t2_4.FormType = "STK"

It works surprisingly quickly now (that FormType filter makes an enormous difference). Even after introducing a couple more tables to complete what I need to report.