SQL statement query with nested clause

Question

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

@jarlh I will further explain the expected results in question. Thanks — S Nash
– S Nash, Commented Sep 1, 2020 at 21:23

Robert Christopher · Accepted Answer · 2020-09-03 09:46:53Z

2

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
                    );

edited Sep 3, 2020 at 9:46

Robert Christopher

5,4901 gold badge22 silver badges25 bronze badges

answered Sep 1, 2020 at 20:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

S Nash Over a year ago

Thanks, I fit this into my actual SQL and will let you know!

S Nash Over a year ago

Your SQL correctly eliminate MrB. But only give me result as MRA and MrB.I want also to see All of their service names.

S Nash Over a year ago

That's it! My real SQL code is far very complex. but your solution applies to that also.

Shabnam Watson · Accepted Answer · 2020-09-02 14:46:54Z

1



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

edited Sep 2, 2020 at 14:46

answered Sep 2, 2020 at 12:23

Shabnam Watson

112 bronze badges

Comments

Mordechai · Accepted Answer · 2020-09-02 14:59:06Z

0

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')

answered Sep 2, 2020 at 14:59

Mordechai

1

Comments

David Buck · Accepted Answer · 2020-09-02 16:54:30Z

-1

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'))

edited Sep 2, 2020 at 16:54

David Buck

3,88840 gold badges54 silver badges74 bronze badges

answered Sep 1, 2020 at 22:01

Chaim Berger

112 bronze badges

4 Comments

S Nash Over a year ago

What is A Here? if you are referring to TableA, your query still does even run.

S Nash Over a year ago

What is 'SELECT NAME FROM A AS A2 WHERE A2.ServiceReceive' ????this SQL is not correct.

Marcus Vinicius Pompeu Over a year ago

@SNash, I revised Chaim`s answer trying to make it clearer.

Chaim Berger Over a year ago

@SNash, The query works perfectly fine. Only Mr A and Mr C are pulling in the result set. If you want to see distinct values just add a DISTINCT clause. And 'A' is the table name, just like it is described in the question "We have a table named A"

Collectives™ on Stack Overflow

SQL statement query with nested clause

4 Answers 4

3 Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related