Multiple columns in table that reference 1 lookup table

Hello,

I have a query that I need help with.

there are two tables...

Product
- ProductId
- Property1
- Property2
- Property3

PropertyType
- PropertyTypeId
- PropertyType

There many columns in (Product) that reverence 1 lookup table (PropertyType)

In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeId

How do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3

ProductId | Property1 | Property2 | Property3 | PropertyType1 | PropertyType2 | PropertyType3

PropertyType(1) = PropertyType for Property1
PropertyType(2) = PropertyType for Property2
PropertyType(3) = PropertyType for Property3

I hope this makes sence.

Thanks in advance.

SELECT Product.ProductId, Product.Property1, Product.Property2, Product.Property3,
Prop1.PropertyType, Prop2.PropertyType, Prop3.PropertyType
FROM Product
INNER JOIN PropertyType Prop1 ON Product.Property1 = PropertyType.PropertyTypeId
INNER JOIN PropertyType Prop2 ON Product.Property2 = PropertyType.PropertyTypeId
INNER JOIN PropertyType Prop3 ON Product.Property3 = PropertyType.PropertyTypeId

|||

Hello,

Two approaches:

1. Left join

SELECT t0.ProductId, t0.Property1, t0.Property2, t0.Property3, t1.PropertyType1, t2.PropertyType2, t3.PropertyType3 from PRODUCT t0 LEFT JOIN (SELECT Product.ProductId, PropertyType.PropertyType AS PropertyType1
FROM Product INNER JOIN PropertyType ON Product.Property1 = PropertyType.PropertyTypeId) t1 ON t0.ProductId= t1.ProductId
LEFT JOIN (SELECT Product.ProductId, PropertyType.PropertyType AS PropertyType2
FROM Product INNER JOIN PropertyType ON Product.Property2 = PropertyType.PropertyTypeId) t2 on t0.ProductId= t2.ProductId
LEFT JOIN (SELECT Product.ProductId, PropertyType.PropertyType AS PropertyType3
FROM Product INNER JOIN PropertyType ON Product.Property3 = PropertyType.PropertyTypeId) t3 on t0.ProductId= t3.ProductId

2. Select Case

SELECT ProductId, Property1, Property2, Property3, CASE WHEN Property1 is not NULL then (SELECT distinct propertyType.propertyType
FROM propertyType INNER JOIN
Product ON propertyType.PropertyTypeId = Product.Property1) END AS propertyType1,
CASE WHEN Property2 is not NULL then (SELECT distinct propertyType.propertyType
FROM propertyType INNER JOIN
Product ON propertyType.PropertyTypeId = Product.Property2) END AS propertyType2, CASE WHEN Property3 is not NULL then (SELECT distinct propertyType.propertyType
FROM propertyType INNER JOIN
Product ON propertyType.PropertyTypeId = Product.Property3) END AS propertyType3

FROM Product

|||Thank you all for your help. I have it working.