Sql-server – Display single record for attributes values joining 3 tables in SQL Server

join;optimizationselectsql server

I have 3 tables

Table1 (a view) contains the record ID, Person's Name, Attribute ID and a new record for all attributes entered into the database pertaining to the person.

Table2 is a descriptive identifier name for the Attribute Type ID in Table1

Table3 (a view) contains the data entry timestamp for the record ID in Table1, used as selection criteria range

Table1 –SQL Server 2014 View–

ID [char(18)]
Name [nvarchar(50)]
AttributeTypeID [int]
AttributeValue [nvarchar(2000)]

Table2 –SQL Server 2014 Table–

AttrTypeID [int]
AttrName [nvarchar(50)]

Table3 –SQL Server 2014 View–

EntryID [char(18)]
EntryDateTime [datetime2(3)]

My current query brings them together (however I would like a different output):

SELECT DISTINCT
Table1.ID
Table1.Name
Table2.AttrTypeID
Table2.AttrName
Table1.AttributeValue

FROM 
Table1 RIGHT JOIN Table2 ON Table1.AttributeTypeID = Table2.AttrTypeID

WHERE
Table1.ID IN (SELECT DISTINCT
             EntryID
             FROM Table3
             WHERE
             EntryDateTime BETWEEN '2018-05-01 00:00:00.000' AND '2018-05-07 00:00:00.000')

ORDER BY
Table1.ID ASC

Returns the following records:

ID     | Name  | AttrTypeID  | AttrName   | AttributeValue
123456 | Bob   | 201         | Hair Color | Blonde
123456 | Bob   | 202         | Eye Color  | Blue
123456 | Bob   | 203         | Height CM  | 180   
123456 | Bob   | 204         | Weight KG  | 77.1   
123456 | Bob   | 205         | Email      | bob@domain.com   
123456 | Bob   | 206         | Profile    | Text...2000characters...  
789000 | Sarah | 201         | Hair Color | Brown
789000 | Sarah | 202         | Eye Color  | Brown
789000 | Sarah | 203         | Height CM  | 155   
789000 | Sarah | 204         | Weight KG  | 50.5  
789000 | Sarah | 206         | Profile    | Text...2000characters...

Note Sarah does not have an entry for email (AttrTypeID = 205) in table 1

My desired output is create a query that returns:

ID     | Name  | Hair Color  | Eye Color  | Height CM  | Weight KG  | Email           | Profile
123456 | Bob   | Blonde      | Blue       | 180        | 77.1       | bob@domain.com  | Text...2000characters...
789000 | Sarah | Brown       | Brown      | 155        | 50.5       | NULL            | Text...2000characters...

Your assistance is very much appreciated!

PS I'm new here so if you need more info or require a different format for asking this question please let me know.

Best Answer

Here is a very simple way to solve this.

--Setup demo data
Declare @Table1 table
    (ID int, Name varchar(5), AttrTypeID int, AttrName varchar(10), AttributeValue varchar(24))
;

INSERT INTO @Table1
    (ID, Name, AttrTypeID, AttrName, AttributeValue)
VALUES
    (123456, 'Bob', 201, 'Hair Color', 'Blonde'),
    (123456, 'Bob', 202, 'Eye Color', 'Blue'),
    (123456, 'Bob', 203, 'Height CM', '180'),
    (123456, 'Bob', 204, 'Weight KG', '77.1'),
    (123456, 'Bob', 205, 'Email', 'bob@domain.com'),
    (123456, 'Bob', 206, 'Profile', 'Text...2000characters...'),
    (789000, 'Sarah', 201, 'Hair Color', 'Brown'),
    (789000, 'Sarah', 202, 'Eye Color', 'Brown'),
    (789000, 'Sarah', 203, 'Height CM', '155'),
    (789000, 'Sarah', 204, 'Weight KG', '50.5'),
    (789000, 'Sarah', 206, 'Profile', 'Text...2000characters...')
;

--The actual query
SELECT ID, max(name) as Name,
  MAX(CASE WHEN AttrName = 'Hair Color' THEN AttributeValue END) AS [Hair Color],
  MAX(CASE WHEN AttrName = 'Eye Color' THEN AttributeValue END) AS [Eye Color],
  MAX(CASE WHEN AttrName = 'Height CM' THEN AttributeValue END) AS [Height CM],
  MAX(CASE WHEN AttrName = 'Weight KG' THEN AttributeValue END) AS [Weight KG],
  MAX(CASE WHEN AttrName = 'Email' THEN AttributeValue END) AS [Email],
  MAX(CASE WHEN AttrName = 'Profile' THEN AttributeValue END) AS [Profile]
FROM @Table1
GROUP BY id, name;

| ID     | Name  | Hair Color | Eye Color | Height CM | Weight KG | Email          | Profile                  |
|--------|-------|------------|-----------|-----------|-----------|----------------|--------------------------|
| 123456 | Bob   | Blonde     | Blue      | 180       | 77.1      | bob@domain.com | Text...2000characters... |
| 789000 | Sarah | Brown      | Brown     | 155       | 50.5      | NULL           | Text...2000characters... |

You just need to wrap your original query inside a common table expression and use that in place of my @Table1. Something like:

;With Table1 as
(
SELECT DISTINCT Table1.ID Table1.NAME Table2.AttrTypeID Table2.AttrName Table1.AttributeValue
FROM Table1
RIGHT JOIN Table2 ON Table1.AttributeTypeID = Table2.AttrTypeID
WHERE Table1.ID IN (
        SELECT DISTINCT EntryID
        FROM Table3
        WHERE EntryDateTime BETWEEN '2018-05-01 00:00:00.000'
                AND '2018-05-07 00:00:00.000'
        )
) 

--The actual query
SELECT ID, max(name) as Name,
  MAX(CASE WHEN AttrName = 'Hair Color' THEN AttributeValue END) AS [Hair Color],
  MAX(CASE WHEN AttrName = 'Eye Color' THEN AttributeValue END) AS [Eye Color],
  MAX(CASE WHEN AttrName = 'Height CM' THEN AttributeValue END) AS [Height CM],
  MAX(CASE WHEN AttrName = 'Weight KG' THEN AttributeValue END) AS [Weight KG],
  MAX(CASE WHEN AttrName = 'Email' THEN AttributeValue END) AS [Email],
  MAX(CASE WHEN AttrName = 'Profile' THEN AttributeValue END) AS [Profile]
FROM Table1
GROUP BY id, name;

Related Solutions

Mysql – Matching single column against multiple values without self-joining table in MySQL

I have found a clever way to do this query without a self join.

I ran these commands in MySQL 5.5.8 for Windows and got the following results:

use test
DROP TABLE IF EXISTS answers;
CREATE TABLE answers (user_id VARCHAR(10),question_id INT,answer_value VARCHAR(20));
INSERT INTO answers VALUES
('Sally',1,'Pouch'),
('Sally',2,'Peach'),
('John',1,'Pooch'),
('John',2,'Duke');
INSERT INTO answers VALUES
('Sally',1,'Pooch'),
('Sally',2,'Peach'),
('John',1,'Pooch'),
('John',2,'Duck');

SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id;

+---------+-------------+---------------+
| user_id | question_id | given_answers |
+---------+-------------+---------------+
| John    |           1 | Pooch         |
| John    |           2 | Duke,Duck     |
| Sally   |           1 | Pouch,Pooch   |
| Sally   |           2 | Peach         |
+---------+-------------+---------------+

This display reveals that John gave two different answers to question 2 and Sally gave two different answers to question 1.

To catch which questions were answered differently by all users, just place the above query in a subquery and check for a comma in the list of given answers to get the count of distinct answers as follows:

SELECT user_id,question_id,given_answers,
(LENGTH(given_answers) - LENGTH(REPLACE(given_answers,',','')))+1 multianswer_count
FROM (SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id) A;

I got this:

+---------+-------------+---------------+-------------------+
| user_id | question_id | given_answers | multianswer_count |
+---------+-------------+---------------+-------------------+
| John    |           1 | Pooch         |                 1 |
| John    |           2 | Duke,Duck     |                 2 |
| Sally   |           1 | Pouch,Pooch   |                 2 |
| Sally   |           2 | Peach         |                 1 |
+---------+-------------+---------------+-------------------+

Now just filter out rows where multianswer_count = 1 using another subquery:

SELECT * FROM (SELECT user_id,question_id,given_answers,
(LENGTH(given_answers) - LENGTH(REPLACE(given_answers,',','')))+1 multianswer_count
FROM (SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id) A) AA WHERE multianswer_count > 1;

This is what I got:

+---------+-------------+---------------+-------------------+
| user_id | question_id | given_answers | multianswer_count |
+---------+-------------+---------------+-------------------+
| John    |           2 | Duke,Duck     |                 2 |
| Sally   |           1 | Pouch,Pooch   |                 2 |
+---------+-------------+---------------+-------------------+

Essentially, I performed three table scans: 1 on the main table, 2 on the small subqueries. NO JOINS !!!

Give it a Try !!!

Sql-server – One or several tables for attributes

A couple of reasons I prefer a single table:

(1) with multiple tables you'll almost always be performing unions and you pay for the core data multiple times

(2) you can easily optimize for a subset of attributes or a combination of devices and attributes with indexes and use sparse columns to minimize wasted space

I wrote a bit about EAV here - it is by no means perfect, but we used it to solve many performance problems (at the cost of others) at a previous job.

Best Answer

Related Solutions

Mysql – Matching single column against multiple values without self-joining table in MySQL

Sql-server – One or several tables for attributes

Related Question