SQL to generate report of entity-attribute-value structured data

Question

I have 3 tables:

Productmaster:

ProductId    PName     PDescription

Attributes:

AttributeID  attName
----         ----
1            Brand
2            Category
3            Artist

ProductAttributeValues

paId   ProductId  AttributeID AttributeValues

A product can have multiple attributes.

Here's the desired output:

ProductId  ProductDesc  Brand    Category    Artist
---        ---          ---      ---         ---
1          sadasd       Brand1   Category1   Artist1
2          sadasds      Brand2   Category3   Artist4

How can I get this output?

Thanks in advance.

Answer 1

Try this query, maybe some tweak required

Select pm.ProductId, pm.PDescription, pav1.AttributeValues, pav2.AttributeValues, pav3.AttributeValues 
from Productmaster pm, ProductAttributeValues pav1, ProductAttributeValues pav2, ProductAttributeValues pav3 
where pm.ProductId = pav1.ProductId 
and pav1.AttributeID = 1 
and pm.ProductId = pav2.ProductId 
and pav2.AttributeID = 2 
and pm.ProductId = pav3.ProductId 
and pav3.AttributeID = 3

Answer 2

You should left join for each attribute value you want to pull in, e.g.

select p.ProductId,p.ProductDesc,
a1.AttributeValues as Brand,
a2.AttributeValues as Category, 
a3.AttributeValues as Artist,
from Product p
left join ProductAttributeValues a1 on(p.ProductId=a1.ProductId and a1.AttributeID=1)
left join ProductAttributeValues a2 on(p.ProductId=a2.ProductId and a2.AttributeID=2)
left join ProductAttributeValues a3 on(p.ProductId=a3.ProductId and a3.AttributeID=3)

To turn this back into English, "give me all products, and for each one, give me a brand, category and artist attribute if they exist"

I've assumed that a product has only one or zero values for each attribute.

Answer 3

Assumed that every attribute appears exactly once per product:

SELECT 
  pm.ProductId as ProductId,
  pm.PDescription as ProductDesc,
  pav_Brand.AttributeValues as Brand,
  pav_Category.AttributeValues as Category,
  pav_Artist.AttributeValues as Artist
FROM
  ProductMaster pm
    inner join ProductAttributeValues pav_Brand 
      on pm.productId == pav_Brand.ProductId
    inner join Attributes a_Brand 
      on pav_Brand.AttributeId = a_Brand.AttributeId
        AND a_Brand.attName = 'Brand'
    inner join ProductAttributeValues pav_Category 
      on pm.productId == pav_Category.ProductId
    inner join Attributes a_Category 
      on pav_Category.AttributeId = a_Category.AttributeId
        AND a_Brand.attName = 'Category'
    inner join ProductAttributeValues pav_Artist 
      on pm.productId == pav_Artist.ProductId
    inner join Attributes a_Artist 
      on pav_Category.AttributeId = a_Artist.AttributeId
        AND a_Brand.attName = 'Artist'

You can use left outer joins if the data is not always available.

Answer 4

SELECT  ProductId, ProductDesc,
        (
        SELECT  AttributeValues
        FROM    ProductAttributeValues pv
        WHERE   pv.AttributeID = 1
                AND pv.ProductID = p.ProductID
        ) AS Brand,
        (
        SELECT  AttributeValues
        FROM    ProductAttributeValues pv
        WHERE   pv.AttributeID = 2
                AND pv.ProductID = p.ProductID
        ) AS Category,
        (
        SELECT  AttributeValues
        FROM    ProductAttributeValues pv
        WHERE   pv.AttributeID = 3
                AND pv.ProductID = p.ProductID
        ) AS Artist
FROM    Products p