Problem creating a view containing lookup tables

Question

Hi there guys, should be simple enough but it's causing me a couple of issues.

I have a data set similar to the following:

User
  UserID
  Name
  Age

UserPropertyValues
  UserID
  PropertyCodeValueID

PropertyCodes
  PropertyCodeID
  PropertyCodeName

PropertyCodeValues
  PropertyCodeValueID
  PropertyCodeID
  PropertValue

Now let's assume the tables contain the following data:

1  John  25
2  Sarah  34

1  2
1  3
2  1
2  3

1  FavColour
2  CarMake
3  PhoneType

1  1  Blue
2  1  Yellow
3  2  Ford
4  3  Mobile
5  3  Landline

Now from this I'm looking to create a view to return the User details, as well as the property values for Property code 1 and 2 like so:

John 25 Yellow Ford
Sarah 34 Blue Ford

The queries I have tried so far tend to return repeating rows of data :

John 25 Yellow
John 25 Ford
Sarah 34 Blue
Sarah 34 Ford

Any help is appreciated, thank you all in advance.

Answer 1

Input data:

DECLARE @User TABLE (UserID INT, Name VARCHAR(10), Age INT)
INSERT INTO @User
SELECT 1,  'John',  25 UNION
SELECT 2,  'Sarah',  34
DECLARE @UserPropertyValues TABLE(UserID INT, PropertyCodeValueID INT)
INSERT INTO @UserPropertyValues
SELECT 1, 2 UNION
SELECT 1, 3 UNION
SELECT 2, 1 UNION
SELECT 2,  3
DECLARE @PropertyCodes 
  TABLE (PropertyCodeID INT, PropertyCodeName VARCHAR(10))
INSERT INTO @PropertyCodes
SELECT 1,  'FavColour' UNION
SELECT 2,  'CarMake' UNION
SELECT 3,  'PhoneType'
DECLARE @PropertyCodeValues TABLE (PropertyCodeValueID INT, 
  PropertyCodeID INT, PropertValue VARCHAR(10))
INSERT INTO @PropertyCodeValues
SELECT 1,  1,  'Blue' UNION
SELECT 2,  1,  'Yellow' UNION
SELECT 3,  2,  'Ford' UNION
SELECT 4,  3,  'Mobile' UNION
SELECT 5,  3,  'Landline'

If two properties is all that you need in result, and each user have those properties, then try this:

SELECT U.Name, U.Age, PCVFC.PropertValue, PCVCM.PropertValue 
FROM @User U
INNER JOIN @UserPropertyValues UPVFC ON U.UserID = UPVFC.UserID 
INNER JOIN @PropertyCodeValues PCVFC 
  ON UPVFC.PropertyCodeValueID = PCVFC.PropertyCodeValueID 
    AND PCVFC.PropertyCodeID = 1
INNER JOIN @UserPropertyValues UPVCM ON U.UserID = UPVCM.UserID 
INNER JOIN @PropertyCodeValues PCVCM 
  ON UPVCM.PropertyCodeValueID = PCVCM.PropertyCodeValueID 
    AND PCVCM.PropertyCodeID = 2

[edit] But to handle possible NULL values better use this:

SELECT U.Name, U.Age, FC.PropertValue, CM.PropertValue 
FROM @User U
LEFT JOIN (
  SELECT UserID, PropertValue FROM @UserPropertyValues  UPV
    INNER JOIN @PropertyCodeValues PCV 
      ON UPV.PropertyCodeValueID = PCV.PropertyCodeValueID 
        AND PCV.PropertyCodeID = 1
) FC ON U.UserID = FC.UserID
LEFT JOIN (
  SELECT UserID, PropertValue FROM @UserPropertyValues  UPV
    INNER JOIN @PropertyCodeValues PCV 
      ON UPV.PropertyCodeValueID = PCV.PropertyCodeValueID 
        AND PCV.PropertyCodeID = 2
) CM ON U.UserID = CM.UserID

Answer 2

SELECT 
  u.[Name],
  u.Age,
  pcv1.PropertValue,
  pcv2.PropertValue
FROM 
  Users  u
  LEFT JOIN 
  ( UserPropertyValues upv1 
    JOIN PropertyCodeValues pcv1 ON 
    upv1.PropertyCodeValueID = pcv1.PropertyCodeValueID 
    AND pcv1.PropertyCodeID = 1
  )
  ON upv1.UserID = u.UserID
  LEFT JOIN (
    UserPropertyValues upv2 
    JOIN PropertyCodeValues pcv2 ON 
    upv2.PropertyCodeValueID = pcv2.PropertyCodeValueID 
    AND pcv2.PropertyCodeID = 2
  )
  ON upv2.UserID = u.UserID

Edit : I renamed user to users

Edit2 : Allow for null (not entered values)

Answer 3

What you really need to is abandon this type of database design as soon as humanly possible. It will never be either effective of efficient. To get three types of values you have to join to the table three times. Once you have 30 or forty differnt types of information, you will need to join to the table that many times (and left joins at that). Further everytime you want any information you will need to join to this table. I see this as creating a major locking issue in your database. The people who originally designed one of the databases I work with did this and caused a huge performance issue when the company grew from having one or two customers to the largest in our industry.

If the properties are ones that will likely only have one realted records per person, put them into the user table. If they will have multiple records then create a separate table for each type of information (one for hones, one for email, one for cartype, etc.) Since the information you will eventually want to collect will usually be more than the simple value and differnt for each type of information they must be in separate tables. Then when you only need to see one value (say phone number but not email) you join to just that table and you aren't interfeing with people trying to access email but not phone number. And if you have a yellow ford or white Honda, it will be stored in only one record in the auto table rather than two property records in your design.