Query Question

Databases
1

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

2

Actually it should give you some errors, because there are some comma’s missing :slight_smile:

And I prefer using INNER JOIN instead of an implicit join:


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
INNER JOIN
    prattsid
ON  prattsid.pr_att_id = pratt.pr_att_id
AND prattsid.site_id = 1
WHERE pratt.product_id = 1
[B][COLOR="Red"]GROUP BY pratt.pr_ty_att_label[/COLOR][/B]

I don’t understand the GROUP BY. Why are you doing that?

3

Thanks, yeah I missed off some commas when I typed it down here.

Originally (an early iteration of the query) I needed to use Group By to stop getting the same rows listed multiple times, but as you pointed out that isn’t necessary now. It works with and without it so may as well remove it.

4

To avoid duplicate lines, you can use SELECT DISTINCT.
GROUP BY gives you distinct values for the columns listed in the group by, and arbitrary values for the other columns (arbitrary means: if there are different values for such a column within a group, it’ll give you only one of those, but it might not be the one you need).