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