SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Query Question

  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Question

    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:
    Code MySQL:
    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,406
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by LuckyB View Post
    This is the query I have so far, and it seems to work
    Actually it should give you some errors, because there are some comma's missing

    And I prefer using INNER JOIN instead of an implicit join:
    Code:
    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
    GROUP BY pratt.pr_ty_att_label
    I don't understand the GROUP BY. Why are you doing that?

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,406
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    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).


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •