Find values from table whose column value determines which column from another table to be used for-CodePudding

I have 2 tables with no relationship defined at DDL.

CREATE TABLE SOURCES(
  ID VARCHAR2(25),
  SOURCE VARCHAR(2),
  VALUE_ID(VARCHAR2(25),
  LAST_UPDATED TIMESTAMP);

CREATE TABLE USERS(
  ID VARCHAR2(25),
  USER_ID VARCHAR(25),
 CLIENT_ID VARCHAR(25));

I need to find all those rows from table sources given an input value for column VALUE_ID. VALUE_ID is set to USERS.USER_ID or USERS.CLIENT_ID based on the SOURCE column of that row.

IF SOURCE = 'A' THEN VALUED_ID = USERS.USER_ID ELSE USERS.CLIENT_ID

Any help resolving this would be much appreciated.

CodePudding user response：

Just use AND and OR in the join condition:

SELECT *
FROM   sources s
       INNER JOIN users u
       ON (  (s.source =  'A' AND s.value_id = u.user_id )
          OR (s.source <> 'A' AND s.value_id = u.client_id))

CodePudding user response：

You can use a CASE statement in your join:

Select *
FROM SOURCES S
INNER JOIN USERS U 
   on S.Value_ID = CASE WHEN S.SOURCE='A' then U.USER_ID ELSE U.CLIENT_ID END