Hi
I am wanting to get data out of the following tables:
Table: pratt (Attribute Table)
pr_att_id
product_id
pr_att_label
pr_att_value
Table: prattsid (Attribute Description Based on Site Table)
pr_att_id
site_id
pr_att_des
Example Data
Table: pratt
pr_att_id,product_id,pr_att_label,pr_att_value
1,1,“Size”,“Large”
2,1,“Colour”,“Blue”
3,1"Weight",“Light”
4,2,“Size”,“Medium”
5,2,“Colour”,“Red”
6,2"Weight",“Very Light”
Table: prattsid
pr_att_id,site_id,pr_att_des
1,1,“Large Size Dress”
1,2,“This Dress is Large”
2,1,“The Dress is blue”
2,2,“This is a blue Dress”
3,1,“The Dress is light weight”
3,2,“This is a light weight dress”
4,1,“Medium Size Dress”
4,2,“This Dress is a Medium size”
5,1,“The Dress is red”
5,2,“This is a red Dress”
6,1,“The Dress is very light weight”
6,2,“This is a very light weight dress”
I want to fetch all the attributes for a product (product_id = 1) and also pull the description of all the attributes for this product that are relevant to a site (site_id = 1).
This is the query I have so far, and it seems to work:
SELECT
pratt.product_id
pratt.pr_ty_att_label
,pratt.pr_att_value
,prattsid.pr_att_des
,prattsid.site_id
,prattsid.pr_att_id
FROM
pratt
prattsid
WHERE (pratt.product_id = 1
AND prattsid.site_id = 1
AND prattsid.pr_att_id =pratt.pr_att_id)
GROUP BY pratt.pr_ty_att_label;
Is there any better/more efficient query that will achieve what I am after?
Thanks