CODEWITHSUNDEEP
sql
Marouane
Marouane

Reputation: 49

SQL Filter based on case statement

I have some logic that is close to the example below:

SELECT
    employee.num,
    CASE job.type
        WHEN 'C' THEN chemical.chem_status
        ELSE physical.phys_status
    END AS attr_status

FROM employee

LEFT JOIN job ON employee.key = job.key

LEFT JOIN physical ON physical.key = job.key

LEFT JOIN chemical ON chemical.key = job.key

WHERE attr_status = 'A'

Any suggestions on how to achieve this?

Thank you.

Upvotes: 0

Views: 710

Answers (2)

Himanshu
Himanshu

Reputation: 3970

You could do a simple where too

  Where (job.type ='C' and chemical.chem_status='A') 
                or (job.type! ='C' and physical.phys_status='A')

Upvotes: 0

Luuk
Luuk

Reputation: 14899

Because in most (or all ?) DBMS system you can not use an alias in the the WHERE-clause, you have 2 options:

  1. Replace attr_status in the WHERE-clause with a copy of the CASE
SELECT
    employee.num,
    CASE job.type
        WHEN 'C' THEN chemical.chem_status
        ELSE physical.phys_status
    END AS attr_status

FROM employee

LEFT JOIN job ON employee.key = job.key

LEFT JOIN physical ON physical.key = job.key

LEFT JOIN chemical ON chemical.key = job.key

WHERE CASE job.type
        WHEN 'C' THEN chemical.chem_status
        ELSE physical.phys_status
      END = 'A'
  1. Make your query a sub-query:
SELECT * 
FROM (
   SELECT
       employee.num,
       CASE job.type
           WHEN 'C' THEN chemical.chem_status
           ELSE physical.phys_status
       END AS attr_status

   FROM employee

   LEFT JOIN job ON employee.key = job.key

   LEFT JOIN physical ON physical.key = job.key

   LEFT JOIN chemical ON chemical.key = job.key 
) x

WHERE x.attr_status = 'A'

Upvotes: 1

Related Questions