Saturday, October 14, 2006

Choosing Only One Feature

Problem posted by Wade Fleming:

I have the following tables:


Person with fields (RecordNum,Name)
Ethnicity with fields (RecordNum,Name)
EthnicityLink with fields (PersonRecNum,EthnicityRecnum)


The idea is that one person can have many ethnicities.
So I could have:
Person: (0001,'Joe Bloggs'),(0002,'John Smith')
Ethnicity: (0001,'Asian'),(0002,'African'),(0003,'European')
EthnicityLink: (0001,0002),(0002,0001),(0002,0003)


meaning that Joe Bloggs is African, and John Smith is European/Asian.


Now I have some rules about what actual ethnicity will be designated to
a given person, one of which is:
- If any of the persons ethnicities are 'European', they are classed as
'European'. (ie: above John Smith would be classed as European)

2005 solution:

CREATE TABLE Person (RecordNum INT,Name VARCHAR(30))
go
CREATE TABLE Ethnicity (RecordNum INT,Name VARCHAR(30))
go
CREATE TABLE EthnicityLink (PersonRecNum INT, EthnicityRecnum INT)
go


INSERT Person VALUES(1,'Joe Bloggs')
INSERT Person VALUES(2,'John Smith')
INSERT Person VALUES(3,'John Doe')
INSERT Ethnicity VALUES(1,'Asian')
INSERT Ethnicity VALUES(2,'African')
INSERT Ethnicity VALUES(3,'European')
INSERT EthnicityLink VALUES(0001,0002)
INSERT EthnicityLink VALUES(0002,0001)
INSERT EthnicityLink VALUES(0002,0003)
INSERT EthnicityLink VALUES(3,0001)
INSERT EthnicityLink VALUES(3,2)
go
SELECT * FROM(
SELECT Person.Name, Ethnicity.Name as Etn_Name,
DENSE_RANK() OVER(PARTITION BY Person.Name ORDER BY CASE Ethnicity.Name
WHEN 'European' THEN 1 ELSE 2 END) rn
FROM Person
INNER JOIN EthnicityLink ON EthnicityLink.PersonRecNum =
Person.RecordNum
INNER JOIN Ethnicity ON Ethnicity.RecordNum =
EthnicityLink.EthnicityRecNum) t
WHERE rn = 1


2000 solution is more involved:

SELECT Person.Name, Ethnicity.Name as Etn_Name
FROM Person
INNER JOIN EthnicityLink ON EthnicityLink.PersonRecNum =
Person.RecordNum
INNER JOIN Ethnicity ON Ethnicity.RecordNum =
EthnicityLink.EthnicityRecNum
WHERE NOT EXISTS(SELECT 1 FROM EthnicityLink el WHERE el.PersonRecNum =
Person.RecordNum
AND el.EthnicityRecNum <> EthnicityLink.EthnicityRecNum AND
el.EthnicityRecNum = 3)

1 Comments:

At 3:38 PM, Blogger dynamicishwar said...

I used temp tables for this one.... Too many in the process but it gets the trick done as well:

select [PersonName],[To],inttest into #temp from
(select
[PersonName],case when [To]='European' then 'European' else [To] end as [To],
ROW_NUMBER() OVER (PARTITION BY [PersonName] order by [PersonName]) as inttest
from
(
select distinct z.name [PersonName],x.name [From],y.name [To]from Person Z,
(select a.RecordNum,name,c.EthnicityRecnum from ethnicity a,EthnicityLink c
where a.RecordNum = c.EthnicityRecnum)
X,
(select a.RecordNum,name,c.EthnicityRecnum from ethnicity a,EthnicityLink c
where a.RecordNum = c.PersonRecNum) Y
where X.EthnicityRecnum =Y.EthnicityRecnum
and z.RecordNum =x.RecordNum
) test1 ) X order by inttest desc

select distinct a.* into #temp1 from #temp a,#temp b
where a.inttest > b.inttest

select distinct a.* into #temp2
from #temp a where a.PersonName not in
(
select #temp1.PersonName from #temp1
)
insert into #temp1 select * from #temp2
drop table #temp2 --dropping this as it contains only the distinct pairs which is now in the main table
select * from #temp1

 

Post a Comment

<< Home