Query to filter the Primary keys when it linked to foreign-key relationship

The below method can be used to filter the primary key records in the Data modelling level.

Using all the category in the group by clause and mark it as 1 for the specific filter that we are trying to get ...and finally in the top sql use sum clause to achieve the result.

create table #indicia

(

number int,

name varchar(20),

carmodel int,

country varchar(20),

LEGISLATION varchar(20),

TYPEnum int

)

select * from #indicia

insert into #indicia values ('1','sara','12','lon','GBR',10)

insert into #indicia values ('1','faizal','12','lon','GBR',0)

insert into #indicia values ('1','sana','12','lon','GBR',0)

insert into #indicia values ('1','arun','12','lon','GBR',3)

;with cenas as

(

select a.number, a.name, a.carmodel, a.country, a.LEGISLATION, sum(a.typenum) as TypeIndicia FROM

(

select t.number, t.name, 1 as typenum, t.carmodel, t.country, t.LEGISLATION, count(1) as Cnt from #Indicia t

where t.typenum = 10

group by t.number, t.name, t.typenum, t.carmodel, t.country, t.LEGISLATION

union

select t.number, t.name, 2 as typenum, t.carmodel, t.country, t.LEGISLATION, count(1) as Cnt  from #Indicia t

where t.typenum = 0

group by t.number, t.name, t.typenum, t.carmodel, t.country, t.LEGISLATION

union

select t.number, t.name, 4 as typenum, t.carmodel, t.country, t.LEGISLATION, count(1) as Cnt  from #Indicia t

where t.typenum not in (0, 10)

group by t.number, t.name, t.carmodel, t.country, t.LEGISLATION) a

group by a.number, a.name, a.carmodel, a.country, a.LEGISLATION

)