sql server - T-SQL Group by with a where clause -

sql server - T-SQL Group by with a where clause -

masterid cc cla des nlcla nldes ------------------------------------- 53006141 cn 0 0 1 1 53006141 1 1 1 1 53006141 uk 1 1 0 0 53006142 1 1 0 0 53006142 uk 1 1 0 0 53006143 cn 0 0 1 1 53006143 1 1 0 0 53006143 uk 1 1 0 0

from above info need produce

a list of masterids there cc = us or cc = cn , nlcla = 1 , nldes = 1

the output should be

53006141 53006143

there has both cn , under masterid.

can help me in sql please?

you can adding where clause homecoming rows either us or cn:

select distinct masterid yourtable cc in ('us', 'cn') , nlcla = 1 , nldes = 1

see sql fiddle demo

if want result include both cn , us, can use:

select masterid yourtable cc in ('us', 'cn') , nlcla = 1 , nldes = 1 grouping masterid having count(distinct cc) = 2

see sql fiddle demo.

another way done using exists list of masterids both us , cn. place other filters in where clause , not in subquery.

select distinct masterid yourtable t1 exists (select masterid yourtable t2 cc in ('us', 'cn') , t1.masterid = t2.masterid grouping masterid having count(distinct cc) = 2) , nlcla = 1 , nldes = 1;

see sql fiddle demo

sql sql-server tsql group-by where-clause