How to create a SQL query for the following operation?

Question

I hope you will be able to help me out. I just started learning SQL and while applying my knowledge at work, I got stuck.

I have SQL Database with multiple tables, which contain various data for properties of the items offered for sale. So far I successfully created a query which pulls most of the information needed. Unfortunately, the last table became problematic for me.

Table is formatted like this:

| fkStockItemId  | PropertyName | PropertyValue | PropertyType |
|   ItemSKU-1    |  Item Style  |   SB-01123    |   Attribute  |
|   ItemSKU-1    |  Item Size   |    X-Small    |   Attribute  |
|   ItemSKU-1    |  Item Color  |      Red      |   Attribute  |
|   ItemSKU-2    |  Item Style  |   AA-66002    |   Attribute  |
|   ItemSKU-2    |  Item Size   |    Medium     |   Attribute  |
|   ItemSKU-2    |  Item Color  |     Green     |   Attribute  |                            
|   ItemSKU-3    |  Item Style  |    110445     |   Attribute  |
|   ItemSKU-3    |  Item Size   |     Small     |   Attribute  |

Output I am trying to get is like this:

    |    SKU    |  Item Style  |  Item Size  | Item Color  | 
    | ItemSKU-1 |   SB-01123   |   X-Small   |    Red      |
    | ItemSKU-2 |   AA-66002   |   Medium    |    Green    |
    | ItemSKU-3 |    110445    |    Small    |   *Null*    | 


    Please note that last column "PropertyType" is for technical purposes and 
is not needed to be queried.

This is what I got so far:

SELECT si.ItemNumber, si.ItemTitle, si.ItemDescription, si.RetailPrice, si.Weight, sl.Quantity, c.CategoryName, siep.ProperyValue, siep.ProperyName
FROM StockItem si
LEFT OUTER JOIN StockLevel sl ON si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ProductCategories c ON si.CategoryId = c.CategoryId
LEFT OUTER JOIN StockItem_ExtendedProperties siep ON si.pkStockItemID = siep.fkStockItemId
WHERE siep.ProperyName = 'Item Style'

Tables "StockLevel" and "ProductCategories" show results just fine. If you notice, last "StockItem_ExtendedProperties" JOIN and "siep.ProperyValue", "siep.ProperyName" coupled with "WHERE siep.ProperyName = 'Item Style'" only allowed me to query 1 property. Thank you for your help and time!

Answer 1

Move the propertyname filter to the ON clause from the WHERE clause. Then join again for each property:

SELECT si.ItemNumber, si.ItemTitle, si.ItemDescription, si.RetailPrice, si.Weight, sl.Quantity, c.CategoryName, style.ProperyValue as style, size.ProperyValue as size
FROM StockItem si
LEFT OUTER JOIN StockLevel sl ON si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ProductCategories c ON si.CategoryId = c.CategoryId
LEFT OUTER JOIN StockItem_ExtendedProperties style ON si.pkStockItemID = style.fkStockItemId
AND style.ProperyName = 'Item Style'
LEFT OUTER JOIN StockItem_ExtendedProperties size ON si.pkStockItemID = size.fkStockItemId
AND size.ProperyName = 'Item Size'