Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

join based upon rows to column

Posted on 2011-09-29
12
Medium Priority
?
340 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 46

Expert Comment

by:Kent Olsen
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 46

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 46

Accepted Solution

by:
Kent Olsen earned 2000 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 46

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
 
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 46

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 46

Expert Comment

by:Kent Olsen
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

885 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