Using IF In SQL Statement

Question

How can I create an sql statement based on a condition?

select Name, Address, Flag from Employees
If Flag = 'Y' then 
join Customers 
on id=id
else
join Clients
on id=id

For each record returned I want to either join to the Customers or the Clients table for additional information

Which RDBMS you are using

Pரதீப்
– Pரதீப்

2015-01-01 18:00:23 +00:00
Commented Jan 1, 2015 at 18:00 — Pரதீப்
– Pரதீப், Commented Jan 1, 2015 at 18:00

Pரதீப் · Accepted Answer · 2015-01-01 18:13:45Z

1

Use two different select queries and combine the result with Union all

SELECT NAME,
       Address,
       Flag
FROM   Employees e
       JOIN Customers c
         ON e.id = c.id
            AND e.Flag = 'Y'
UNION ALL
SELECT NAME,
       Address,
       Flag
FROM   Employees e
       JOIN Clients c
         ON e.id = c.id
            AND (e.Flag IS NULL OR e.Flag <> 'Y')

edited Jan 1, 2015 at 18:13

answered Jan 1, 2015 at 18:08

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

peterm · Accepted Answer · 2015-01-01 18:07:52Z

1

One way to do it

SELECT e.Name,
       e.Address,
       e.Flag,
       CASE
         WHEN e.flag = 'Y'
           THEN c.some_column
         ELSE l.other_column
       END AS additional_info
FROM   Employees e
       LEFT JOIN Customers c
         ON e.id = c.employee_id
            AND e.flag = 'Y'
       LEFT JOIN Clients l
         ON e.id = l.employee_id
            AND e.flag = 'N'

Here is a SQLFiddle demo

edited Jan 1, 2015 at 18:07

answered Jan 1, 2015 at 18:02

peterm

93k16 gold badges157 silver badges162 bronze badges

Collectives™ on Stack Overflow

Using IF In SQL Statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related