How to Select from tree column

Question

0.00/5 (No votes)

See more:

SQL

CREATE TABLE RefType
(
id INT PRIMARY KEY NOT NULL,
name NVARCHAR(50) NOT NULL
)

CREATE TABLE Ref
(
id INT PRIMARY KEY NOT NULL,
name NVARCHAR(50) NOT NULL,
rt INT NOT NULL FOREIGN KEY REFERENCES RefType(id)
)

INSERT INTO RefType VALUES(1, 'Gender')
INSERT INTO RefType VALUES(2, 'Hair color')

INSERT INTO Ref VALUES(1, 'Male', 1)
INSERT INTO Ref VALUES(2, 'Female', 1)
INSERT INTO Ref VALUES(3, 'Black', 2)
INSERT INTO Ref VALUES(4, 'Brown', 2)

CREATE TABLE Person
(
name NVARCHAR(50),
gender INT FOREIGN KEY REFERENCES RefType(id),
hair INT FOREIGN KEY REFERENCES RefType(id)
)

INSERT INTO Person VALUES('John', 1, 2)

Question: How i do query on this base. My want to result is:
name : John
gender: Male
hair: Brown

Posted 30-Apr-13 21:48pm

Ч. Цэрэнпүрэв

Updated 30-Apr-13 23:41pm

Sandeep Mewara

v2

Add a Solution

Comments

RelicV 1-May-13 8:44am

Hey, do you want them in a table like structure
COL 1 | COL 2
-------|------
name | John
gender | Male
hair | Brown

or you want the result as following
Name|Gender|Hair
John|Male |Brown

Ч. Цэрэнпүрэв 16-May-13 3:08am

Name|Gender|Hair
John|Male |Brown

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2013-05-01T04:24:00

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

SQL

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