Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: List only rows with multiple values (complex SQL)

    Hello and thank you to all in advance!

    Here is the scenario:

    I have the following tables in my database:

    1) products p (keyroducts_id)
    2) products_description pd
    3) products_to_categories p2c
    4) products_options po
    5) products_options_values pov
    6) products_attributes pa

    I want to query the tables to fetch all products in one category, which have multiple attributes.

    For instance if we have:

    Product ID - Attributes
    12 - 10
    132 - 9
    19 - 10
    232 - 8
    12 - 9
    34 - 10

    I want the results to show Product IDs that have Attribute ID 10 and 9. i.e. Product ID 12.

    To achieve this, I am trying to do the following:

    Code:
    SELECT p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, p.products_price_sorter, p.products_qty_box_status
      FROM products p, products_description pd, products_to_categories p2c, products_options po , products_options_values pov, products_attributes pa
    INNER
      JOIN ( SELECT products_id, options_values_id
               FROM products_attributes
              WHERE products_attributes.options_values_id IN (15, 20)
             GROUP
                 BY p.products_id 
             HAVING COUNT(*) = 2 ) AS pa2
        ON pa2.products_id = p.products_id
    I am clearly not an SQL expert so I have no idea why the above is not working, and I would really appreciate if someone could tell me how I can achieve the results I desire.

    I tried the following SQL too:

    Code:
    select DISTINCT p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, p.products_price_sorter, p.products_qty_box_status, pa.options_values_id
    FROM products p, products_description pd, products_to_categories p2c, products_options po , products_options_values pov, products_attributes pa 
    WHERE p.products_status = 1 
    AND p2c.categories_id = 66 
    AND p.products_price >= 0 
    AND p.products_price <= 1000000 
    AND p.products_quantity > 0
    AND pa.options_values_id IN (15, 20)
    AND p.products_id = pd.products_id
    AND p.products_id = p2c.products_id
    AND p.products_id = pa.products_id
    GROUP BY p.products_id
    HAVING COUNT(pa.options_values_id)=2;
    But this doesn't give me any results because the COUNT for options_values_id runs into thousands.

    If I take the 'HAVING COUNT...' line out, I get two rows...

    One with options_values_id 15 and 20, and the other with 15 only. This is the one I do not want listed in the results. Only those records with both 15 and 20.

    Any help will be greatly appreciated!

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT p.products_id
         , pd.products_name
         , p.products_price
         , p.products_tax_class_id
         , p.products_image
         , p.products_price_sorter
         , p.products_qty_box_status
      FROM products p
    INNER
      JOIN ( SELECT products_id
               FROM products_attributes
              WHERE options_values_id IN (15, 20)
             GROUP
                 BY products_id 
             HAVING COUNT(*) = 2 ) AS pa2
        ON pa2.products_id = p.products_id
    INNER
      JOIN products_description pd
        ON pd.products_id = p.products_id
    INNER    
      JOIN products_to_categories p2c
        ON p2c.products_id = p.products_id
       AND p2c.categories_id = 66 
     WHERE p.products_status = 1 
       AND p.products_price BETWEEN 0 AND 1000000 
       AND p.products_quantity > 0
    note i removed products_options and products_options_values from the query because it appears you didn't need them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2012
    Posts
    2
    Thanks for your swift response!

    It worked like a charm. Thank you!

Posting Permissions

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