CODEWITHSUNDEEP
oracle-database
comp_user
comp_user

Reputation: 60

how to find clients that meet the same criteria in one column

I have a column that is like this:

Client ID Service Name
123456 housing
122458 transportation
125837 education
125837 transportation
173947 Childcare

I am trying to extract data from an Oracle database for all clients who have accessed education and transportation services only. Could anyone please help me write my query?

I have created a Where statement in my query that goes like this:

where ch.service_name IN ('education', 'transportation)

however, this query gives me all clients who have accessed education and or transportation when in fact, I only want data for clients who have accessed both education and or transportation.

thank you

Upvotes: 0

Views: 36

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

Seems you need a HAVING Clause along with GROUP BY after restricting to only those two service_name such as

SELECT ClientID
  FROM t
 WHERE service_name IN ('education', 'transportation')
 GROUP BY ClientID
HAVING COUNT(DISTINCT service_name) = 2

EDIT(depending on the comment) : One option would be converting the query into the one which contains a COUNT() analytic function such as

WITH t2 AS
(
SELECT t.*, COUNT(DISTINCT service_name) OVER (PARTITION BY ClientID) AS cnt      
  FROM t
 WHERE service_name IN ('education', 'transportation')
)
SELECT *
  FROM <the_other_table>
  LEFT JOIN t2
 WHERE cnt = 2

in order to combine with your current query as desired.

Upvotes: 1

Related Questions