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)