Solved

join based upon rows to column

Posted on 2011-09-29
12
285 Views
Last Modified: 2013-11-16
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

0
Comment
Question by:sam2929
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 36890851
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36891203
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
 
LVL 45

Expert Comment

by:aikimark
ID: 36891455
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
 

Author Comment

by:sam2929
ID: 36892576
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
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 36893009
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
 
LVL 45

Expert Comment

by:aikimark
ID: 36893061
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Expert Comment

by:sanofi-aventis
ID: 36893366
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
 

Author Comment

by:sam2929
ID: 36910799
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
 
LVL 45

Expert Comment

by:aikimark
ID: 36910845
@sam2929

Have you looked at my comments?
0
 

Author Comment

by:sam2929
ID: 36911482
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
 

Author Comment

by:sam2929
ID: 36923563
no reply :(
0
 
LVL 45

Expert Comment

by:Kdo
ID: 36923882
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now