Sql - How to load the "Attribute Value"

Question

attribute [table]

• attribute_id
• attribute_name
• category_id

product_attribute [table]

• group_id
• attribute_id
• attribute_value

What's the sql statment should be if I want to have a table as like below

Attribute Name Attribute Value

CPU Model
CPU Speed
Memory Size
HDD Size

Answer 1

You can use INNER JOIN to list both columns:

SELECT a.attribute_name AS [Attribute Name], p.attribute_value AS [Attribute Name]
FROM attribute AS a
INNER JOIN product_attribute AS p ON a.attribute_id = p.attribute_id;

Answer 2

Sorry but no, your query does not include "category_id" and "group_id". And this does show the correct result.

What I am looking for is something like this but the following query does not show any data once the "product_attribute" table has no data included.

SELECT a.attribute_name, p.attribute_value
FROM attribute AS a
INNER JOIN item_attribute AS p ON a.attribute_id = p.attribute_id
WHERE a.category_id = 123 AND p.group_id = 10

Answer 3

Hi @Ihandler ,

Left join can return all the values of the left table (even if there is no matching value in the right table),please refer to below sql:

SELECT  a.attribute_name [Attribute Name],a. category_id,b.group_id,b.attribute_value [Attribute Value]  
FROM attribute  a  
left JOIN product_attribute p   
ON a.attribute_id = p.attribute_id  

If this doesn't solve your problem，please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result? So that we’ll get a right direction and make some test.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo