CODEWITHSUNDEEP
sql-servert-sql
S Nash
S Nash

Reputation: 2509

SQL statement query with nested clause

For presentation purposes I modify the table names and fields and simplify the SQL problem:

We have a table named A with only 2 columns:

Name   ServiceReceived
=========================
Mr A   Medical
Mr A   Dental
Mr A   HealthCare
Mr A   Special1
Mr B   Dental
Mr B   HealthCare
Mr C   Medical
Mr C   Dental
Mr C   HealthCare
Mr C   Special
Mr C   Special2

I need a list of all people who revised medical or dental.

And ALSO received one or two of Special1 and Special2

I tried

where ServiceReceived in ('medical', 'dental')
  and ServiceReceived in ('Special1', 'Special2')

But this does not work.

======================================

Expected Results:(As you see MrB is not there, since he does not have Special1 or Special2

 Name   ServiceReceived
    =========================
    Mr A   Medical
    Mr A   Dental
    Mr A   HealthCare
    Mr A   Special1 
    Mr C   Medical
    Mr C   Dental
    Mr C   HealthCare
    Mr C   Special
    Mr C   Special2

Upvotes: 0

Views: 108

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use aggregation and a having clause:

    select t.*
    from t
    group by Name
    having sum(case when ServiceRecived in ('Medical', 'Dental') then 1 else 0 end) > 0 and
           sum(case when ServiceRecived in ('Special1', 'Special2') then 1 else 0 end) between 1 and 2;

If you want to see all the details, you can use join, in or exists:

    select t.*
    from t
    where t.name in (select Name
                     from t
                     group by Name
                     having sum(case when ServiceRecived in ('Medical', 'Dental') then 1 else 0 end) > 0 and
                            sum(case when ServiceRecived in ('Special1', 'Special2') then 1 else 0 end) between 1 and 2
                    );

Upvotes: 2

Chaim Berger
Chaim Berger

Reputation: 11

This should work.

SELECT NAME
FROM A
WHERE
    NAME IN (SELECT NAME FROM A AS A2 WHERE A2.ServiceReceived IN ('medical', 'dental'))
    AND NAME IN (SELECT NAME FROM A AS A2 WHERE A2.ServiceReceived IN ('Special1', 'Special2'))

Upvotes: -1

Mordechai
Mordechai

Reputation: 1

The Following solution works with the SQL server INTERSECT table operator.

SELECT NAME 
FROM T 
WHERE ServiceReceived IN ('medical', 'dental') 
INTERSECT 
SELECT NAME 
FROM T WHERE 
ServiceReceived IN ('Special1', 'Special2')

Upvotes: 0

Shabnam Watson
Shabnam Watson

Reputation: 11



Select Name 
from 
(
Select Name,
       Sum(Case when ServiceRequested = 'Medical' then 1 else 0 end) as Medical,
       Sum(Case when ServiceRequested = 'Dental' then 1 else 0 end) as Dental,
       Sum(Case when ServiceRequested IN ('Special1','Special2') then 1 else 0 end) as Specials
from [dbo].[Services]
Group by Name
)s
Where  ( Medical >= 1 or Dental >= 1 ) AND Specials >= 1

Upvotes: 1

Related Questions