SitePoint Sponsor

User Tag List

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

    Complex (?) SQL Query

    I am wanting to pull a list of all product attributes that belong to a particular product (product_id = 15) from one table, and from another product pull each attributes description.
    If the description is empty for that product AND site (in the case site_id = 2) relation (every site has potential for a unique description) then I want to pull the default description (site_id = 1).

    This is what I have so far, and it isn't working properly.
    SELECT
    pa.product_id
    , pa.pr_ty_at_g_la AS group_label
    , pa.pr_ty_at_g_sor
    , pa.pr_ty_att_label AS attribute_label
    , pa.pr_att_value AS attribute_value
    , pasd.pr_att_des AS attribute_description

    FROM
    pratt AS pa

    INNER JOIN
    ( SELECT COALESCE(NULLIF(optl.pr_att_des,''),NULLIF(dflt.pr_att_des,'')) AS pr_att_des FROM prattsid AS dflt
    LEFT OUTER
    JOIN prattsid AS optl
    ON optl.pr_att_id = dflt.pr_att_id
    AND optl.site_id = 2
    WHERE dflt.site_id = 1 ) AS pasd
    WHERE pa.product_id = 15 AND pa.pr_ty_at_g_la='Other'

    Thanks

  2. #2
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Example data from pratt:
    HTML Code:
    pr_att_id	product_id	pr_ty_att_id	pr_ty_at_g_sor	pr_att_value	pr_ty_att_label	pr_ty_at_g_la
    3	        15	          3	           0	         19.24	       Interest Rate	Other
    Example data for prattsid
    HTML Code:
    pr_att_id	site_id	pr_att_des
    3	       1          This is default description for Interest Rate
    3	       2          This is site 2's description for Interest Rate
    So with the above descriptions it would pick "This is site 2's description for Interest Rate" as the description.

    However with the following data it would pick "This is default description for Interest Rate" as the description for the current site (site_id = 2) is blank(or possibly null):
    HTML Code:
    pr_att_id	site_id	pr_att_des
    3	       1          This is default description for Interest Rate
    3	       2          

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT
        pa.product_id
      , pa.pr_ty_at_g_la AS group_label
      , pa.pr_ty_at_g_sor
      , pa.pr_ty_att_label AS attribute_label
      , pa.pr_att_value AS attribute_value
      , COALESCE(optl.pr_att_des, dflt.pr_att_des, '') AS attribute_description
    FROM pratt AS pa
    LEFT OUTER JOIN prattsid AS optl
    ON optl.pr_att_id = pa.pr_att_id
    AND optl.site_id = 2
    LEFT OUTER JOIN prattsid AS dflt
    ON dflt.pr_att_id = pa.pr_att_id
    AND dflt.site_id = 1
    WHERE pa.product_id = 15 AND pa.pr_ty_at_g_la='Other'

  4. #4
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much, works a treat!


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
  •