join based upon rows to column

Hi,
We have tABLE  TD1
ID      Tpart       func     postalcode  PF_KEY

1000     1001     00000009    k2w0c6       1
1000     1001     00000Z03    48211        2
1002     1003     00000Z03    k333          3
1004     1005     000Z04      m333          4

Then we have TD2

ID      PF_009     PF_Z03       PF_Z04
1000     1001      1001          1003
1004     1005      1003          1003

so we want to match td1 with td2 based
upon id
if TD1..func '009'
TD1.TPART=PF_009
IF TD1..func 'Z03'
TD1.TPART=PF_Z03

so basic idea is get last three digits of func match Tpart with PF_ columns(as PF_Columns data is same as Tpart)

so final result should be after the joins


ID      PF_009     PF_Z03       PF_Z04   PF_009_KEY   PF_Z03_KEY
1000     1001      1001          1003        1           2
1004     1005      1003          1003        0           0

sam2929Asked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi Sam,

Sorry...  The cryptic names aren't easy to follow and I mistyped one of them...

When td2 is joined the second time, we just need to use the correct column for the join key.

LEFT JOIN td2 t1
  ON t0.id = t1.id
 AND t0.PF_009 = t1.tpart
LEFT JOIN td2 t2
  ON t0.id = t1.id
 AND t0.PF_Z03  = t2.tpart;

I think that that's what you're looking for.

Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Sam,

It looks like joining TD1 to TD2 twice should do this.

See the code below.


Good Luck,
Kent


SELECT t0.ID, t0.FP0009, t0,FP_Z03, t0.FP_Z04, 
  coalesce (T1.pf_key, '0') pf_009_key,
  coalesce (t2.pf_key, '0') pf_z03_key
FROM  td1 t0
LEFT JOIN td2 t1
  ON t0.id = t1.id
 AND t0.PF_009 = t1.tpart
LEFT JOIN td2 t2
  ON t0.id = t1.id
 AND t0.PF_009  = t2.tpart;

Open in new window

0
 
momi_sabagCommented:
you might be able to do it using a single join and a case clause
but the solution suggested by Kent will probably perform better
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
aikimarkCommented:
Can we assume correctly that you want to update TD1 data?

==============
You should normalize TD2
ID	PFname	PFkey	PFdata
1000	PF_009	009	1001
1000	PF_Z03	Z03	1001
1000	PF_Z04	Z04	1003
1004	PF_009	009	1005
1004	PF_Z03	Z03	1003
1004	PF_Z04	Z04	1003

Open in new window


With only three columns, you should be able to write the SQL by hand.  This is a three part UNION query.  I'll refer to this as UQTD2
Select ID, "PF_009" As PFname, "009" As PFkey, PF_009 As PFdata
From TD2
UNION ALL

Select ID, "PF_Z03" As PFname, "Z03" As PFkey, PF_Z03 As PFdata
From TD2
UNION ALL

Select ID, "PF_Z04" As PFname, "Z04" As PFkey, PF_Z04 As PFdata
From TD2

Open in new window


You should be able to join the UNION results directly with TD1 with

TD1.ID = UQTD2.ID and Right(TD1.func,3) = UQTD2.PFkey
0
 
sam2929Author Commented:
Hi Kent,
We do need PF_Z03 =t1.tpart to get records for Z03  can we do that in 1 sql?

LEFT JOIN td2 t1
  ON t0.id = t1.id
 AND t0.PF_009 = t1.tpart
LEFT JOIN td2 t2
  ON t0.id = t1.id
 AND t0.PF_009  = t2.tpart;
0
 
aikimarkCommented:
You could also do a regular join between TD1 and TD2 in a UNION query.
Select TD1.ID, Tpart, func, postalcode, PF_KEY, PF_009 As PFdata
From TD1 Inner Join TD2 On TD1.ID=TD2.ID
Where Right(TD1.func, 3)="009"

UNION ALL

Select TD1.ID, Tpart, func, postalcode, PF_KEY, PF_Z03 As PFdata
From TD1 Inner Join TD2 On TD1.ID=TD2.ID
Where Right(TD1.func, 3)="Z03"

UNION ALL

Select TD1.ID, Tpart, func, postalcode, PF_KEY, PF_Z04 As PFdata
From TD1 Inner Join TD2 On TD1.ID=TD2.ID
Where Right(TD1.func, 3)="Z04"

Open in new window


===========
To get the data in form you requested without normalization, you will need to create new column expressions in your query.

Select Distinct ID, (Select Top 1 PF_009 From TD2 Where TD2.ID=TD1.ID) As PF_009, 
(Select Top 1 PF_Z03 From TD2 Where TD2.ID=TD1.ID) As PF_Z03, 
(Select Top 1 PF_Z04 From TD2 Where TD2.ID=TD1.ID) As PF_Z04, 
(Select Top 1 T1PK.PF_Key From TD1 T1PK Where T1PK.ID=TD1.ID And 
Right(T1PK.func,3)="009") As PF_009_KEY, 
(Select Top 1 T1PK.PF_Key From TD1 T1PK Where T1PK.ID=TD1.ID And 
Right(T1PK.func,3)="Z03") As PF_Z03_KEY
From TD1

Open in new window

0
 
sanofi-aventisCommented:
sam2929,

You might consider doing this logic before you transpose you data to the wide format. Refering to your earlier question (change column data to rows ).
It might be easier to do it before transposing.

T-Bone
0
 
sam2929Author Commented:
Hi kent,
This will not work reason is as Tpart has multiple records so who come PF_009 knows
to just get func 00000009 record

We have tABLE  TD1
ID      Tpart       func     postalcode  PF_KEY

1000     1001     00000009    k2w0c6       1
1000     1001     00000Z03    48211        2
1002     1003     00000Z03    k333          3
1004     1005     000Z04      m333          4


Then we have TD2

ID      PF_009     PF_Z03       PF_Z04
1000     1001      1001          1003
1004     1005      1003          1003


LEFT JOIN td2 t1
  ON t0.id = t1.id
 AND t0.PF_009 = t1.tpart
LEFT JOIN td2 t2
  ON t0.id = t1.id
 AND t0.PF_Z03  = t2.tpart;
0
 
aikimarkCommented:
@sam2929

Have you looked at my comments?
0
 
sam2929Author Commented:
Yes i did but i really don't want create new column expressions the reason i am saying so is
we building star schema based upon dimensions and fact and left outer join i beleive should do it.
0
 
sam2929Author Commented:
no reply :(
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Sam,

You weren't abandoned.  Every time I go back to the original question I read something slightly different than the time before.  The path that aikimark pursued seemed to make as much sense to me as what I had done so I was sitting back.

Can you help me to understand your final results a bit better?  I don't see how you get to them.

ID      PF_009     PF_Z03       PF_Z04   PF_009_KEY   PF_Z03_KEY
1000     1001      1001          1003        1           2
1004     1005      1003          1003        0           0

Open in new window


-- There are 4 rows in TD1, with ID keys of 1000, 1000, 1002, and 1004.  
-- ID 1002 doesn't show up in the final results.  
-- PF_Z03_KEY has a value of 2 for key 1000, but only 1 row for 1000 references Z03.

Apparently, I'm just not understanding how you're combining the two tables into that results.  Can you clarify the process for me?


Thanks,
Kent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.