CODEWITHSUNDEEP
sqldb2where-clause
Chuck
Chuck

Reputation: 11

Using Same Column multiple time in SQL WHERE clause

I am looking for a way to write a query that returns results where columns can be used multiple times in a WHERE statement. Can't find the best way to do this.

SELECT
  EMPLOYEE, CATEGORY, STATUS
FROM
  EMPLOYMENT
WHERE 
  (CATEGORY = 1 AND STATUS LIKE '%A%') AND (CATEGORY = 2 AND STATUS = 'B')

Upvotes: 0

Views: 124

Answers (3)

R. Hoek
R. Hoek

Reputation: 1106

Basically you are already using the using the column name multiple times (EX. CATEGORY = 1 and CATEGORY = 2).

But the question is not clear enough, to give you an actual answer for the question.

Inner join:

SELECT e1.EMPLOYEE, e1.STATUS
FROM EMPLOYMENT as e1
Inner join EMPLOYMENT as e2 on
  e2.EMPLOYEE = e1.EMPLOYEE and e2.STATUS = ‘B’ and e2.CATEGORY = 2
WHERE (e1.CATEGORY = 1 AND e1.STATUS LIKE '%A%')

This will show all employment record for the employees with CATEGORY = 1 and all the statusses containing an A, for which the same employee also has STATUS B in CATEGORY 2

It should return the same result as the query of @Daniel

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Presumably, you want employees that have these conditions. If so, you can use aggregation:

SELECT EMPLOYEE
FROM EMPLOYMENT
WHERE (CATEGORY = 1 AND STATUS LIKE '%A%') OR
      (CATEGORY = 2 AND STATUS = 'B')
GROUP BY EMPLOYEE
HAVING COUNT(DISTINCT CATEGORY) = 2;  -- matches both

Upvotes: 1

Daniel Marcus
Daniel Marcus

Reputation: 2686

Looks like you want 'Or' here:

    SELECT EMPLOYEE, CATEGORY, STATUS 
      FROM EMPLOYMENT 
      WHERE CATEGORY = 1 AND STATUS LIKE '%A%' 
and employee in (select EMPLOYEE from EMPLOYMENT where 
           CATEGORY = 2 AND STATUS = 'B')

Upvotes: 1

Related Questions