Home » SQL & PL/SQL » SQL & PL/SQL » join field depending on condition (DB Visualizer SQL ORACLE)
join field depending on condition [message #612600] Tue, 22 April 2014 10:43 Go to next message
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 #612602 is a reply to message #612600] Tue, 22 April 2014 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Surely if you post a test case along with the result you want for it.

Re: join field depending on condition [message #612604 is a reply to message #612602] Tue, 22 April 2014 11:14 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: join field depending on condition [message #612605 is a reply to message #612604] Tue, 22 April 2014 12:05 Go to previous messageGo to next message
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 #612606 is a reply to message #612605] Tue, 22 April 2014 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A test case (please read the links) is CREATE TABLE and INSERT statements.
We can't execute and test anything from what you posted.

Re: join field depending on condition [message #612610 is a reply to message #612606] Tue, 22 April 2014 12:41 Go to previous messageGo to next message
epr55
Messages: 12
Registered: May 2011
Location: PR
Junior Member
Sorry... It is that I have no idea how to do it, so I don't have any code done.
Re: join field depending on condition [message #612612 is a reply to message #612610] Tue, 22 April 2014 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

CREATE TABLE A (memberid ...);
INSERT INTO A VALUEs (...);
and so on.

[Updated on: Tue, 22 April 2014 12:49]

Report message to a moderator

Re: join field depending on condition [message #612615 is a reply to message #612612] Tue, 22 April 2014 12:59 Go to previous messageGo to next message
epr55
Messages: 12
Registered: May 2011
Location: PR
Junior Member
Although I know this is totally incorrect, it is the best I could do for now of what my I nned to do


SELECT A.MEMBERID1, A.MEMBERID2, B.RISKSCORE

FROM PATIENTS A
CASE WHEN A.CSO_CUS_ID in (select B.MEMBERID1, count(MEMBERID1)
from RISCORES B
group by MEMBERID1
having count(MEMBERID1) >1)
LEFT JOIN RISCORES B
on (A.MEMBERID2=B.MEMBERID2)

ELSE LEFT JOIN RISCORES B on (A.MEMBERID1=B.MEMBERID1)
Re: join field depending on condition [message #612617 is a reply to message #612615] Tue, 22 April 2014 13:12 Go to previous message
Michel Cadot
Messages: 59147
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

Previous Topic: Data Type Number
Next Topic: Job not being executed on time
Goto Forum:
  


Current Time: Sun Sep 21 03:43:47 CDT 2014

Total time taken to generate the page: 0.05632 seconds