Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

matching two tables

Posted on 2011-09-23
2
Medium Priority
?
319 Views
Last Modified: 2013-11-16
I have a table

AA

aa_id    lang    englishdesc  otherdesc
101    O                    BBBB
101    E       hello  

102    O                     KKK
102    E        OTHER
 
103    O                     HHHH
103    E        TEST

BB

bb_ID  level1_id  
101   102
102   102
103   101


I WANT to join aa with bb based upon aa_id and bb_id and want to have final level1_id get englishdesc and other desc
based upon leve11_id

so final results should look like AA will be master table

final table



aa_id    lang    englishdesc  otherdesc level1_id englishlevel1desc  otherlevel1desc
101    O                    BBBB          102        kkk
101    E       hello                      102                            other

102    O                     KKK          102        kkkk                 other
102    E        OTHER                     102
 
103    O                     HHHH         101         BBB        
103    E        TEST                      101                             hello


so leve1_id will match with aa_id to get englishlevel1desc and otherleve1desc
0
Comment
Question by:sam2929
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 36585844
Hi:

data AA;
infile cards truncover dlm='|' dsd;
informat aa_id 3.   lang $1.    englishdesc $20. otherdesc $20.;
input aa_id    lang    englishdesc  otherdesc;
cards;
101|O||BBBB
101|E|hello|
102|O||KKK
102|E|OTHER|
103|O||HHHH
103|E|TEST|
;
RUN;


data BB;
infile cards truncover dlm='|' dsd;
informat bb_ID 3. level1_id 3. ;
input bb_ID  level1_id  ;
cards;
101|102
102|102
103|101
;
RUN;

PROC SQL;
  create table NewTable1 as
  select * from work.AA as aa
left join work.BB as bb
on aa.aa_id = bb.bb_id;

 create table NewTable2 as
   select New1.*, aa.englishDesc as  englishlevel1desc, aa.OtherDesc as otherlevel1desc
   from work.NewTable1 as New1
   left join
   work.aa as aa
   on New1.level1_id = AA.aa_id and New1.lang = AA.lang
   order by New1.aa_id, New1.lang DESC;
quit;

Open in new window

0
 
LVL 2

Accepted Solution

by:
sanofi-aventis earned 2000 total points
ID: 36710501
I thought I would take a crack at this. I think this might work.

Sincerely,
T-Bone
PROC SQL;

	CREATE TABLE FINAL AS 
	SELECT DISTINCT
		   A1.aa_id as aa_id,
	       A1.lang,
		   CASE WHEN A1.lang = 'E'
		        THEN A1.englishdesc
				ELSE '' END as englishdesc,
		   CASE WHEN A1.lang = 'O'
		        THEN A1.otherdesc
				ELSE '' END as otherdesc,
		   BB.level1_id as level1_id,
		   CASE WHEN A2.lang = 'E'
		        THEN A2.englishdesc
				ELSE '' END as englishlevel1desc,
		   CASE WHEN A2.lang = 'O'
		        THEN A2.otherdesc
				ELSE '' END as otherlevel1desc
	  FROM AA AS A1,
	       AA as A2,
		   BB AS BB
	 WHERE A1.aa_id = BB.bb_id
	   AND A2.aa_id = BB.level1_id
	   AND A1.lang  = A2.lang;

QUIT;

Open in new window

0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

604 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