CODEWITHSUNDEEP
sqlsql-server
Jess8766
Jess8766

Reputation: 417

SQL Server : selecting rows that contain only certain values?

Hopefully someone can help with this I am struggling to get my head around it.

I have a table called tblAgreements that looks like this:

AgreementID | GroupDescription |   Live
------------+------------------+--------
20549       |        h&s       |    1
20549       |    construction  |    1
20549       |        HR        |    1
20549       |      Legal       |    1

My aim is to pull all agreement ID's that have only H&S and construction group descriptions. If an agreementID has h&s, construction as well as any other values (like in my example) - it won't be picked up. Only those that have H&S, construction or both.

My query looks like this

select * 
from tblagreements 
where groupdescription in ('construction', 'h&s')

This would bring back something similar to my example table, which isn't what I would want as there are also HR/Legal groupdescriptions for that agreementID

Does that make sense? Hopefully someone can help me get my head around this!

Thanks as always

Upvotes: 2

Views: 3827

Answers (5)

Eralper
Eralper

Reputation: 6612

Following solution provides Agreements with numbers that have both 'h&s' and 'construction', but no any other

;with cte as (
    select *
    from tblAgreements
    where GroupDescription in ('h&s' , 'construction') 
), cnts as (
    select cte.AgreementID, count(*) over (partition by cte.AgreementID) as cnt
    from cte
    inner join tblAgreements as a on a.AgreementID = cte.AgreementID and a.GroupDescription = cte.GroupDescription
)
select *
from cte where AgreementID in (
    select AgreementID from cnts where cnt = 2
)

Upvotes: 0

Ullas
Ullas

Reputation: 11556

Find total count and count of h&s and construnction in GroupDescription column. Then select AgreementID having both the count as 2.

Query

;with cte as (
    select [AgreementID]
    , count(*) as [total]
    , sum(case [GroupDescription] 
          when 'h&s' then 1
          when 'construction' then 1
          else 0 end
    ) as [count]
    from [t]
    group by [AgreementID]
)
select [AgreementID]
from cte
where [total] = 2 and [count] = 2;

Upvotes: 0

Phojie Rengel
Phojie Rengel

Reputation: 1

SELECT * FROM tblagreements
WHERE GroupDescription='H&S';

try this. Pulling only data in Groupdesription column that output 'H&S'

Upvotes: -1

Yván Ecarri
Yván Ecarri

Reputation: 1739

Try this:

select * from tblagreements t1
where groupdescription in ('construction', 'h&s')
and not exists 
     (select * 
     from tblagreements t2 where 
     t1.AgreementID=t2.AgreementID
     and groupdescription not in ('construction', 'h&s'))

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use the following query:

select AgreementID  
from tblagreements
group by AgreementID 
having count(case when groupdescription in ('construction', 'h&s') then 1 end) >= 0 
       and 
       count(case when groupdescription not in ('construction', 'h&s') then 1 end) = 0

to get the ids of the agreements that meet your requirements. Then use this query as a derived table in a join operation to get the rest of the fields.

Upvotes: 5

Related Questions