Your schema is wrong - the
Person
Table should not be referencing table
RefType
but table
Ref
. As things stand you have no link to table
Ref
from table
Person
. In other words you cannot query for the data you are asking for.
Make that change and you will also need to change your insert into the
Person
table
INSERT INTO Person VALUES('John', 1, 4)
Your normalisation is also a little strange - two different entity types in table
Ref
with another table defining the attribute type is probably a level of normalisation too far.
However, you can get the results you want (after making the changes above) with this sql query
select #Person.name, Gender.gender, Hair.hair
from #Person
left outer join
(select #Ref.id, #Ref.name as gender from #Ref inner join #RefType on #Ref.rt=#RefType.id
where #RefType.name = 'Gender') as Gender on #Person.gender=Gender.id
left outer join
(select #Ref.id, #Ref.name as hair from #Ref inner join #RefType on #Ref.rt=#RefType.id
where #RefType.name = 'Hair color') as Hair on #Person.hair=Hair.id
I hope you agree that it looks pretty awful, but it returns the following results
name gender hair
John Male Brown