1

Please check the SQL schema and query on SQL Fiddle

I'm getting repetitive records with NULL values, if anyone can rectify the problem.

Regards

This is what I am getting:

| MEM_ID |      MEM_EMAIL | GENDER | EDUCATION |  PROFESSION |
|--------|----------------|--------|-----------|-------------|
|      1 |   [email protected] |   Male |    (null) |      (null) |
|      1 |   [email protected] | (null) |  Graduate |      (null) |
|      1 |   [email protected] | (null) |    (null) |    Engineer |
|      2 | [email protected] | Female |    (null) |      (null) |
|      2 | [email protected] | (null) |  Graduate |      (null) |
|      2 | [email protected] | (null) |    (null) | Not Working |

but I need

| MEM_ID |      MEM_EMAIL | GENDER | EDUCATION |  PROFESSION |
|--------|----------------|--------|-----------|-------------|
|      1 |   [email protected] |   Male |  Graduate |    Engineer |
|      2 | [email protected] | Female |  Graduate | Not Working |

|

Improve this question
2
  • The fiddle is a great set up but could you describe more about what the issue is. What is your expected outcome? Commented Dec 20, 2014 at 1:31
  • What??????????? (sorry for all the ? stupid min chars) Commented Dec 20, 2014 at 1:35

4 Answers 4

Reset to default
5

Ah yes, the famous Inner-Platform effect, where you try to implement relations by creating "attribute-value" tables and assigning magic strings for data types and values, then try to retrieve values with massive self-joins at runtime.

Only madness lies down this road. SQL already includes features for enforcing key values and referential integrity; don't try to implement this yourself. It's especially frustrating because your schema is actually quite simple:

CREATE TABLE [dbo].Member(
    ID INT PRIMARY KEY,
    Email Varchar(50) NOT NULL,
    GenderID INT NOT NULL,
    EducationID INT,
    ProfessionID INT
)

CREATE TABLE [dbo].Gender(
    GenderID INT PRIMARY KEY,
    GenderName Varchar(50) NOT NULL
)

CREATE TABLE [dbo].Education(
    EducationID INT PRIMARY KEY,
    EducationName Varchar(50) NOT NULL
)

CREATE TABLE [dbo].Profession(
    ProfessionID INT PRIMARY KEY,
    ProfessionName Varchar(50) NOT NULL
)

Assign your magic values to Gender, Education, and Profession rows and assign their IDs to Member. You can perform full lookups with a simple:

SELECT ID, Email, GenderName, EducationName, ProfessionName
FROM Member m
    JOIN Gender g ON g.GenderID=m.GenderID
    LEFT JOIN Education e ON e.EducationID=m.EducationID
    LEFT JOIN Profession p ON p.ProfessionID=m.ProfessionID
WHERE ...

You want to enforce values? Make the Member columns NOT NULL. Want to allow, say, only a single instance of each Education row per member? Foreign-key constraints already support this, no need to invent your own query language.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

:) Thanks - I'm trying to use only 1 lookup table instead of many as you suggested and there will be around 15-18 lookup tables (string values) so do you think I should be using 1 lookup table or many?
You should be using as many lookup tables as there are things you look up. Trying to merge different data types into one table seldom ends well.
no different data types string values only
@GeoConcepts, Gender and Education should be considered different
Gender, Education, and Profession are all different data types, the fact you can represent them all with strings is irrelevant. If they were the same data type, you should be able to assign someone a profession of "Female" or a gender of "Graduate".
3

I think you are basically trying to do a pivot on your data. This is one way to accomplish that.

SELECT      M.mem_Id,
            M.mem_email,
[Gender] = (select max( A.att_value)
            from tbl_attributes A 
             inner join tbl_mem_att_values MAV 
               on MAV.att_id = A.att_id
             inner join tbl_types T
               on T.type_id = A.type_id
              where T.type_name = 'Gender' 
              and MAV.mem_Id = M.mem_Id),

[Education] = (select max( A.att_value)
            from tbl_attributes A 
             inner join tbl_mem_att_values MAV 
               on MAV.att_id = A.att_id
             inner join tbl_types T
               on T.type_id = A.type_id
              where T.type_name = 'Education' 
               and MAV.mem_Id = M.mem_Id),

[Profession] = (select max( A.att_value)
            from tbl_attributes A 
             inner join tbl_mem_att_values MAV 
               on MAV.att_id = A.att_id
             inner join tbl_types T
               on T.type_id = A.type_id
              where T.type_name = 'Profession' 
                and MAV.mem_Id = M.mem_Id)
FROM        tbl_members M

The result looks like this

EM_ID   MEM_EMAIL   GENDER  EDUCATION   PROFESSION
1   [email protected]    Male    Graduate    Engineer
2   [email protected]  Female  Graduate    Not Working
Improve this answer

4 Comments

I'm going to add few more lookup tables - so do you think should I use separate lookup tables or current schema?
you say 18 lookups? It is a lot and your ERD will look messier but @Dour is right that the SQL needed for every query you ever write on that table will be very complicated if you stick with the ultra normalized schema. What you have is very normalized but there is a balance to be struck.
This is not “very normalized”; it is anti-normalized. He is trying to store different data types in the same column, which violates First-Normal Form.
@DourHighArch you're right. It is just a tempting road to go down when you are learning about this stuff: to put everything that looks similar in one table to reduce the number of tables and tag it all with some meta data to make a "table inside a table". But these should all be different tables. They are all different things.
3

Here's what you need...

SQL Fiddle

SELECT M.mem_Id,
M.mem_email,
(   SELECT AA.att_value 
    FROM tbl_mem_att_values mv
    JOIN tbl_attributes AA ON AA.att_id = mv.att_id
    JOIN tbl_types TG ON TG.type_name = 'Gender' AND TG.type_id = aa.type_id
    WHERE mv.mem_id = M.mem_Id) AS Gender,

(   SELECT AA.att_value 
    FROM tbl_mem_att_values mv
    JOIN tbl_attributes AA ON AA.att_id = mv.att_id
    JOIN tbl_types TG ON TG.type_name = 'Education' AND TG.type_id = aa.type_id
    WHERE mv.mem_id = M.mem_Id) AS Education,

(   SELECT AA.att_value 
    FROM tbl_mem_att_values mv
    JOIN tbl_attributes AA ON AA.att_id = mv.att_id
    JOIN tbl_types TG ON TG.type_name = 'Profession' AND TG.type_id = aa.type_id
    WHERE mv.mem_id = M.mem_Id) AS Profession

FROM tbl_members M

Brad beat me by 6 seconds

Improve this answer

Comments

1

Just as an alternative is you want to stick to the joins:

SELECT      M.mem_Id,
            M.mem_email,
            AA.att_value AS Gender,
            AB.att_value AS Education,
            AC.att_value AS Profession
FROM        tbl_members M

JOIN tbl_mem_att_values mavA ON M.mem_Id = mavA.mem_id
JOIN tbl_mem_att_values mavB ON M.mem_Id = mavB.mem_id
JOIN tbl_mem_att_values mavC ON M.mem_Id = mavC.mem_id

JOIN tbl_types TA ON TA.type_name = 'Gender'
JOIN tbl_types TB ON TB.type_name = 'Education'
JOIN tbl_types TC ON TC.type_name = 'Profession'

LEFT JOIN tbl_attributes AA ON mavA.att_id = AA.att_id AND TA.type_id = AA.type_id
LEFT JOIN tbl_attributes AB ON mavB.att_id = AB.att_id AND TB.type_id = AB.type_id
LEFT JOIN tbl_attributes AC ON mavC.att_id = AC.att_id AND TC.type_id = AC.type_id

WHERE AA.type_id IN (TA.type_id, TB.type_id, TC.type_id)
AND AB.type_id IN (TA.type_id, TB.type_id, TC.type_id)
AND AC.type_id IN (TA.type_id, TB.type_id, TC.type_id)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.