?
Solved

matching two tables

Posted on 2011-09-23
2
Medium Priority
?
306 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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