CODEWITHSUNDEEP
sqldb2
Marcus Vinícius Monteiro
Marcus Vinícius Monteiro

Reputation: 8069

SQL - SELECT from a table if a certain condition is met, else SELECT from another

How do I do this in SQL:

  1. SELECT CLIENT, PAYMENT_CODE FROM PAYMENTS_TABLE
  2. If PAYMENT_CODE = 1, SELECT other data from table_1
  3. Else, SELECT other data from table_2

The results I want are:

| Client | Payment Code = 1 | Address from table_1 | Phone from table_1 | ...

or

| Client | Payment Code  1 | Address from table_2 | Phone from table_2 | ...

Thanks in advance!

Upvotes: 0

Views: 1459

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

Although Rajesh's solution is fine, you can also do it using two LEFT JOINs;

SELECT pt.CLIENT, pt.PAYMENT_CODE, 
       COALESCE(t1.address, t2.address) address, 
       COALESCE(t1.phone,   t2.Phone)   phone
FROM PAYMENTS_TABLE pt
LEFT JOIN Table_1 t1 
  ON pt.client_code = t1.client_code AND pt.PAYMENT_CODE =  1
LEFT JOIN Table_2 t2 
  ON pt.client_code = t2.client_code AND pt.PAYMENT_CODE <> 1

EDIT: Added client_code as linking criteria according to a comment to another answer.

Upvotes: 2

user330315
user330315

Reputation:

You didn't tell us how table_1 and table_2 are linked to payments_table, but something like this should work:

select p.client, 
       p.payment_code,
       case 
          when payment_code = 1 then t1.address
          else t2.address
       end as address,
       case 
          when payment_code = 1 then t1.phone
          else t2.phone
       end as phon
from payments_table p
   left join table_1 t1 on p.some_column = t1.some_column
   left join table_2 t2 on p.some_column = t2.come_column

Upvotes: 1

radar
radar

Reputation: 13425

you need to use UNION , one SELECT query to get details from table_1 when payment_code =1 and another SELECT to get details from table_2 when payment_code is not equal to 1

SELECT CLIENT, PAYMENT_CODE, T1.Address, T1.Phone FROM PAYMENTS_TABLE
JOIN Table_1 T1
ON -- your condition
WHERE PAYMENT_CODE =1
UNION 
SELECT CLIENT, PAYMENT_CODE, T2.Address, T2.Phone FROM PAYMENTS_TABLE
JOIN Table_2 T2
ON -- your condition
WHERE PAYMENT_CODE <> 1

Upvotes: 1

Related Questions