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!

Greg Viers · Accepted Answer · 2015-06-23T19:43:03.580

0

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'

edited Jun 23 '15 at 19:43

answered Jun 23 '15 at 19:29

Greg Viers

3,473
3
18
36

Hmm, query give me following error: The correlation name 'style' is specified multiple times in a FROM clause. – Aman Khan Jun 23 '15 at 19:37
Sorry, I named the table the same thing both times I joined it. Try the edited version up now. – Greg Viers Jun 23 '15 at 19:39
Ok nvm I see where is the problem – Aman Khan Jun 23 '15 at 19:39
Wow this is beautiful. Ok 1 little thing. Column "style" shows multiple styles for items as intended. But the last column "size" actually shows "Item Size" repeated for every single item? And thank you very much for your help! – Aman Khan Jun 23 '15 at 19:42
I'm being too sloppy, but I hope you get the general idea. You join in the same property table multiple times under different aliases for the properties you need. I have edited it again. – Greg Viers Jun 23 '15 at 19:43
Brilliant! This answer is the first one and correct one! Thank you! – Aman Khan Jun 23 '15 at 19:47

score 0 · Answer 2 · answered Jun 23 '15 at 19:40

0

To get each value in a column like that, you can first write a subquery for each property, and then JOIN them all together, like this:

SELECT m1.fkStockItemId, m1.propertyValue AS 'Item Style', m2.propertyValue AS 'Item Size', m3.propertyValue AS 'Item Color'
FROM(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Style') m1
LEFT JOIN(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Size') m2 ON m2.fkStockItemId = m1.fkStockItemId
LEFT JOIN(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Color') m3 ON m3.fkStockItemId = m2.fkStockItemId;

Here is an SQL Fiddle example.

answered Jun 23 '15 at 19:40

AdamMc331

16,492
10
71
133

Hello! Thank you for contribution. Your answer is basically a different way of doing it? Honestly, I never even imagined SQL can be so flexible. Not in w3schools website where I started to learn it! – Aman Khan Jun 23 '15 at 19:49
There are probably better ways, as this one seems kinda clunky, but it was the first thing that came to mind. Does it work? – AdamMc331 Jun 23 '15 at 19:50
I am trying it now. Even if the first answer did exactly how I wanted, I am curious trying other ways as well. – Aman Khan Jun 23 '15 at 19:59

JACA_001 · Answer 3 · 2015-06-23T20:05:45.000

I believe that the best way is to make a function that will return the value of property you need and what you include in the query.

CREATE FUNCTION dbo.GetItemProperty
(
    @ItemName AS VARCHAR(50)
    , @Property AS VARCHAR(8)
)
RETURNS VARCHAR(50)
AS
BEGIN

    DECLARE @Ans AS VARCHAR(50) = ''

    SELECT  @Ans = PropertyValue 
    FROM    StockItem AS si
    JOIN    StockItem_ExtendedProperties AS siep 
            ON si.pkStockItemID = siep.fkStockItemId
    WHERE   si.pkStockItemID = @ItemName
            AND siep.ProperyName = @Property

    RETURN @Ans;

END
GO

SELECT  si.ItemNumber, 
        si.ItemTitle, 
        si.ItemDescription, 
        si.RetailPrice, 
        si.Weight, 
        sl.Quantity, 
        c.CategoryName, 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Style') AS 'Item Style', 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Size') AS 'Item Size', 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Color') AS 'Item Color'
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;

God luck!

Is this for SQL? I can enter this function directly as a query? — Aman Khan, Jun 23 '15 at 20:05
The above query creates a function for SQL Server. First run the function that creates the function, then you run the select. Know what I mean? — JACA_001, Jun 23 '15 at 20:08

How to create a SQL query for the following operation?

3 Answers3