-1

I have a sql statement

SELECT emp.id, emp.name, 
FROM emp, office, payment
WHERE emp.id = office.empid 
AND payment.id = emp.pay

but I actually want the 

 AND payment.id = emp.pay

to be executed conditionally. It should not execute always. For e.g. it should execute only if emp.pay is NOT NULL

Mohammad Shahbaz
  • 403
  • 8
  • 28
  • 1
    The left join in the answer below is the right technique. But you really need to stop using a comma separated list of tables. The ANSI-92 style joins have been available now for almost 25 years. Time to step up your game. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins – Sean Lange Oct 11 '18 at 18:24

2 Answers2

0

i think your condition will be like below

SELECT emp.id, emp.name, 
FROM emp, office, payment
WHERE emp.id = office.empid 
or (payment.id = emp.pay and emp.pay is not null)

and I prefer explicit join like below

SELECT emp.id, emp.name, 
    FROM emp join
    office on emp.id = office.empid
   left join payment on payment.id = emp.pay
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
0

You can use CASE WHEN to form a conditional in your WHERE clause, but your code is a terrible habit when it comes to joins. You should not do joins like that.

If you want to make those values match only when emp pay is not null, you can write an inner join.

SELECT emp.id, 
emp.name, 
FROM emp 
INNER JOIN office
ON emp.id = office.empid 
LEFT JOIN payment 
ON payment.id = emp.pay
WHERE emp.pay IS NOT NULL

A few rows of sample data would help to confirm how you want the output to look.

dfundako
  • 8,022
  • 3
  • 18
  • 34