CODEWITHSUNDEEP
sql-server
Alon Shmiel
Alon Shmiel

Reputation: 7121

Select all rows that has only specific value in records

I have a table that contains id, type.

I want to select all the ids that have only one or more records of the same type.

For example,

Assuming this is my table:

 id    type
456     4 
123     4 
123     4 
123    18 
123     4 
789     4 
789     4 
000     7

I want to get ids: 456,789 cause those ids have only records with type = 4:

456 has one record, and 789 has two records of type = 4.

123 has type = 4, but has type = 18.

How can I do it?

I know I can use partition, but I want something like join/exists..

http://sqlfiddle.com/#!9/731e1

Upvotes: 1

Views: 61

Answers (3)

Migo
Migo

Reputation: 151

I don't think @M.Ali answer mets your critera. His resultset includes id = '000'

if OBJECT_ID('Tempdb..#Work') is not null
    drop table #Work;
Create Table #Work (Id char(3), [Type] int)
insert into #Work values
 ( '456',  4) 
, ('123',  4) 
, ('123',  4) 
, ('123', 18) 
, ('123',  4) 
, ('789',  4) 
, ('789',  4) 
, ('000',  7)

select distinct * 
from #Work a 
where exists (
                select Type 
                    ,Count(Distinct Id) cntId
                from #Work b
                where a.Type = b.Type 
                group by Type
                having Count(Distinct Id) > 1
            )
and exists (
        select Id
            ,count(distinct Type)
        from #Work c
        where a.Id = c.Id
        group by id
        having count(distinct type)= 1
    )

output:

Id   Type
---- -----------
456  4
789  4

Upvotes: 0

M.Ali
M.Ali

Reputation: 69494

Select Id 
FROM cards
GROUP BY Id
HAVING COUNT(DISTINCT [type]) = 1

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use:

SELECT id
FROM cards
GROUP BY id
HAVING MIN(type) = MAX(type)

Demo here

Upvotes: 4

Related Questions