List only rows with multiple values (complex SQL)

[markjson]

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:

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:

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

 

[feherke]

Hi

Sorry, the task is not really clear to me, so I react only to this sentence for now :

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

[b]select[/b]
productid

[b]from[/b] markjson

[b]where[/b] attributeid [b]in[/b] [teal]([/teal][purple]9[/purple][teal],[/teal][purple]10[/purple][teal])[/teal]

[b]group[/b] [b]by[/b] productid

[b]having[/b] count[teal]([/teal][b]distinct[/b] attributeid[teal])=[/teal][purple]2[/purple]

The key point there : [tt]count(distinct)[/tt]. That makes sure both 9 and 10 were encountered at least once.

Feherke.

http://feherke.github.com/

 

[r937]

try this --

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


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon