CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Kevin M
Kevin M

Reputation: 5496

How to write SQL query for following scenario

I have a table in following structure.

Group   Member/Owner  Type

G1       M1           Member
G1       OW1          Owner
G2       OW1          Owner
G3       OW1          Owner
G3       OW2          Owner
G4       M2           Member
G4       OW2          Owner

Now, I want to query all Groups that has only Owner and do not have a single member.

The required query should return the groups G2 and G3 for above table since it has only owners and not a single member.

Can anyone help me to achieve this?

Upvotes: 1

Views: 76

Answers (4)

tinto
tinto

Reputation: 151

Using Left Outer Join:

SELECT G1 FROM 

    (SELECT GROUPS G1, TYPE T1 FROM temp 
    WHERE TYPE = 'Owner') TAB1
        LEFT JOIN
    (SELECT GROUPS G2, TYPE T2 FROM temp 
    WHERE TYPE = 'Member') TAB2
        ON TAB1.G1 = TAB2.G2

WHERE T2 IS NULL
GROUP BY G1;

Upvotes: 0

mohan111
mohan111

Reputation: 8865

declare @Table1 TABLE 
    (Groups varchar(2), Owner varchar(3), Type varchar(6))
;

INSERT INTO @Table1
    (Groups, Owner, Type)
VALUES
    ('G1', 'M1', 'Member'),
    ('G1', 'OW1', 'Owner'),
    ('G2', 'OW1', 'Owner'),
    ('G3', 'OW1', 'Owner'),
    ('G3', 'OW2', 'Owner'),
    ('G4', 'M2', 'Member'),
    ('G4', 'OW2', 'Owner')
;

;WITH CTE AS (
Select T.Groups,T.Type,T.Owner from (
select Groups, Owner, Type,ROW_NUMBER()OVER(PARTITION BY Groups ORDER BY Type)RN from @Table1
)T WHERE T.RN = 1 AND T.Type <> 'member')

Select * from @Table1 T WHERE EXISTS (Select 1 from CTE WHERE Groups = T.Groups)

Upvotes: 0

Akshey Bhat
Akshey Bhat

Reputation: 8545

    Select [group] from
    (
Select [group], 
sum( case when type='member' then 1 else 0 end) as cntmember,
   sum( case when type='owner' then 1 else 0 end) as cntowner
     from tab group by [group]) tab
    where cntmember=0 and cntowner=1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can do this with aggregation and having:

select [group]
from t
group by [group]
having min(type) = 'Owner' and max(type) = 'Owner';

This says that the minimum and maximum value of type is 'Owner' -- which means that the value is always 'Owner' (or possibly NULL).

Alternatively, you can express the having clause as:

having sum(case when type = 'Member' then 1 else 0 end) = 0

Upvotes: 3

Related Questions