Java Curious ღ Java Curious ღ - 3 months ago 7
MySQL Question

How to display record or data of single item from 3 joins

I am trying to display data from 3 separate tables.

1. product_master that contain data regarding products like image,price,quantity,etc.

2. attribute_master that contain only attribute_id and attribute_name,

3. product_attrib_master that contain data like prod_id i.e foreign key, attribute_id i.e. foreign key and product_attribute_value.

Now I have fire following query :

SELECT
pm.prod_name,
am.attribute_name,
pa.product_attribute_value
FROM product_attrib_master pa
LEFT JOIN attribute_master am
ON pa.attribute_id = am.attribute_id
LEFT JOIN product_master pm
ON pa.prod_id=pm.prod_id
ORDER BY pa.prod_id;


this query shows following result :

enter image description here

Now I want to display data of only specific item.
Suppose I want to display data of item
Nokia Lumia 925
and it's
prod_id = 12
.
Then what I will have to do ?

Anyone suggest me what I ll have to do ?

Answer

You need to add a where clause to your query. Try this::

SELECT
 pm.prod_name,
 am.attribute_name,
 pa.product_attribute_value
 FROM product_attrib_master pa
 LEFT JOIN attribute_master am
 ON pa.attribute_id = am.attribute_id
 LEFT JOIN product_master pm
 ON pa.prod_id=pm.prod_id
WHERE pm.prod_name ='Nokia Lumia 925'
 and prod_id = 12
 ORDER BY pa.prod_id;