CODEWITHSUNDEEP
sql-server
Geo Concepts
Geo Concepts

Reputation: 187

SQL Query Error

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 |

|

Upvotes: 1

Views: 86

Answers (4)

fejese
fejese

Reputation: 4628

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)

Upvotes: 1

Dour High Arch
Dour High Arch

Reputation: 21711

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.

Upvotes: 5

Spock
Spock

Reputation: 4900

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

Upvotes: 3

Brad
Brad

Reputation: 12245

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

Upvotes: 3

Related Questions