Joining product attributes table with the product table to display product

Question

I have three tables for listing products with product attributes

Product Table with dummy data

Product_Attributes with dummy data

Attributes with dummy data

Kespersky antivirus (productid = 1) has no attributes but the iPhone (productid =2) has two attributes applicable to it, memory and resolution both in Attribute table which has its value stored in Product_Attribute table.

How do I join these tables to show/display both the products with there corresponding attributes?

EDIT

I need to display these products as

Answer 1

The following will work for any number of attributes:

select product.productId, product.name,
group_concat(concat(attr.attributeName, ":", pa.attributeValue))
from product
left outer join product_attributes pa 
on (pa.productId = product.productId)
left outer join attributes attr 
on (attr.attributeId = pa.attributeId)
group by product.productId, product.name

Answer 2

Your question requires a pivot, which needs to be predefined. Meaning, if you want to include 2 extra COLUMNS in your result set, your query can then only store up to 2 attributes. This is a PRESENTATION layer problem, not query layer. But alas, I have a general solution for you. It assumes you will have a max number of 2 attributes (for the reasons states above). Here is the query:

SELECT 
  P.ProductName,
  A.AttributeName,
  PA.AttributeValue,
  B.AttributeName,
  PB.AttributeValue
FROM lb_products P
LEFT JOIN (select row_number() over (partition by productID order by AttributeID asc) rn, * 
           from lb_product_attributes x) PA
  ON P.ProductID = PA.ProductID and PA.rn = 1
LEFT JOIN (select row_number() over (partition by productID order by AttributeID asc) rn, * 
           from lb_product_attributes x) PB
  ON P.ProductID = PB.ProductID and PB.rn = 2
LEFT JOIN lb_attributes A
  ON PA.AttributeID = A.AttributeID
LEFT JOIN lb_attributes B
  ON PB.AttributeID = B.AttributeID;

And the SQL Fiddle for you to play around. Good luck! And feel free to ask any questions :)

http://sqlfiddle.com/#!6/49a9e0/5

Answer 3

You can try this:

SELECT 
  P.ProductName,
  P.Price,
  -- Add other Column Here
  A.AttributeName,
  PA.AttributeValue
FROM Product P
LEFT JOIN Product_Attributes PA
  ON P.ProductID = PA.ProductID
LEFT JOIN Attributes A
  ON PA.AttributeID = A.AttributeID

Output

ProductName   Price    AttbituteName    AttributeValue
Kaspersky     380      NULL             NULL   
IPHONE        45000    Memory           64 gb
IPHONE        45000    Resolution       21500 pi

Answer 4

Philip's answer is certainly good, and he spells out the problem in that you need to define a static number of attributes if you're doing a pivot. I'm not sure the windowed functions are necessary, though, so here's how I'd do it:

select
  prd.productId
  ,max(case when lpa.attributeId = 1 then attributeName else null end) attributeName1
  ,max(case when lpa.attributeId = 1 then attributeValue else null end) attributeValue1
  ,max(case when lpa.attributeId = 2 then attributeName else null end) attributeName2
  ,max(case when lpa.attributeId = 2 then attributeValue else null end) attributeValu2
from
  lb_products prd
  left outer join lb_product_attributes lpa on lpa.productId = prd.productId
  left outer join lb_attributes atr on atr.attributeId = lpa.attributeId
group by
  prd.productId