CODEWITHSUNDEEP
sqloracle-database
Boris Mehta
Boris Mehta

Reputation: 1

How to write a SQL Statement in Oracle SQL with two AND conditions applying on same column within same table?

I need help to write a Oracle SQL query, details are below:

I have a table offender_gang_affiliations with two different columns named offender_id and dept_dvsn_code.

I want a list of all offender id's for which there are rows in the table with a dept_dvsn_code of 'A' as well as dept_dvsn_code of 'J'. I've tried applying various AND and OR conditions but I haven't been able to come up with anything that worked.

Upvotes: 0

Views: 118

Answers (1)

Justin Cave
Justin Cave

Reputation: 231791

It sounds like you want something like

SELECT offender_id
  FROM offender_gang_affiliations
 WHERE dept_dvsn_code IN ('A','J')
 GROUP BY offender_id
HAVING COUNT(DISTINCT dept_dvsn_code) = 2

Another way of formulating the query would be

SELECT offender_id
  FROM offender_gang_affiliations
 WHERE dept_dvsn_code = 'A'
INTERSECT
SELECT offender_id
  FROM offender_gang_affiliations
 WHERE dept_dvsn_code = 'J'

Generally, I would expect the first option to be more efficient. Depending on the developer, the second option may end up being clearer.

Upvotes: 3

Related Questions