| join field depending on condition [message #612600] |
Tue, 22 April 2014 10:43  |
 |
epr55
Messages: 12
Registered: May 2011
Location: PR
|
Junior Member |
|
|
Hi
I am a begginer in SQL and there is a task I need to work on that would like to know the best way to do it. I used the programm DB Visualizer wich ORACLE server.
This is the scenario:
Two tables: Table A and Table B which have two fields in common: MemberID1 and MemberID2. From Table B, I want to obtain the RiskScore
The trick is this. There are cases with the same MemberID1 , but different MemberID2. If I just either join by both MemberID1 and MemberID2 or just by MemberID2 I can missed cases and no RiskScore assigned. If join by MemberID1, I will get duplicate values. What I have to do is for the cases in which in TABLE B there are more than one B.MemberID1 (COUNT(B.MemberID1>1) I will join by B.MemberID2. If it is just B.MemberID1=1, the join is with MemberID1>1.
Is there is any way to accomplish this?
|
|
|
|
|
|
|
|
| Re: join field depending on condition [message #612605 is a reply to message #612604] |
Tue, 22 April 2014 12:05  |
|
epr55
Messages: 12
Registered: May 2011
Location: PR
|
Junior Member |
|
|
Great, thanks. Here is an example:
Table A
MemberID1 MemberID2
123 AA
567 CC
Table B
MemberID1 MemberID2 RiskScore
123 AA 1.3
123 BB 2.5
567 D1 3.1
Desired Result:
A.MemberID1 A.MemberID2 B.RiskScore
123 AA 1.3
567 CC 3.1
This is, for the first case in Table A MemberID1=123, even though in table B the MemberID2 have multiple values of RiskScore because the MemberID2 changed, to get the correct RiskScore the join needs to be with the MemberID2.
For the second case, there is only one record, but in order to obtain the RiskScore, the join needs to be using the MemberID1 since the MemberID2 are not equal..
[Updated on: Tue, 22 April 2014 12:06] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: join field depending on condition [message #612617 is a reply to message #612615] |
Tue, 22 April 2014 13:12 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> SELECT A.MEMBERID1, A.MEMBERID2, B.RISKSCORE
2
3 FROM PATIENTS A
4 CASE WHEN A.CSO_CUS_ID in (select B.MEMBERID1, count(MEMBERID1)
5 from RISCORES B
6 group by MEMBERID1
7 having count(MEMBERID1) >1)
8 LEFT JOIN RISCORES B
9 on (A.MEMBERID2=B.MEMBERID2)
10
11 ELSE LEFT JOIN RISCORES B on (A.MEMBERID1=B.MEMBERID1)
12 /
CASE WHEN A.CSO_CUS_ID in (select B.MEMBERID1, count(MEMBERID1)
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
