Joining table with conditional sql

Question

I have tables as below

table A

emp_code | emp_name

table B

emp_code | id

table C

emp_code | id

I want to get emp_name so I do:

SELECT a.emp_name 
  FROM A a, B a 
 WHERE a.emp_code = b.emp_code

Now I want to capture a case when id in table B is not null and greater than 0 then it should compare a.emp_code with emp_code from table C (like SELECT c.emp_code FROM C c, B b WHERE c.id = b.id) otherwise do as above.

Answer 1

Hard to tell what you are looking for, but you could start from here:

SELECT *
       ,COALESCE(c.id, b.id) AS chosen_id
FROM A AS a
LEFT JOIN B AS b
    ON a.emp_code = b.emp_code 
LEFT JOIN C AS c
    ON c.emp_code = a.emp_code
    AND b.id IS NOT NULL
    AND b.id > 0

Answer 2

You could UNION two SELECTs, the first on B excluding records with 0 or null, and the second on C including only records where B has 0 or null.

Answer 3

Your description of what you want doesn't make much sense, so I tried two differnt queries that might get you want you want (If b has a code why do you want c's code, wouldn't you really want b's code in that case?):

SELECT a.emp_name , case when b.emp_code >0 or b.id is not null then c.emp_code, else b.emp_code end as emp_code 
FROM A a
LEFT JOIN B b 
    ON a.emp_code = b.emp_code 
LEFT JOIN C c 
    ON c.emp_code = b.emp_code 

SELECT a.emp_name , case when b.emp_code <0 or b.emp_code is null then c.emp_code, else b.emp_code end as emp_code 
FROM A a
LEFT JOIN B b 
    ON a.emp_code = b.emp_code 
LEFT JOIN C c 
    ON c.emp_code = a.emp_code