[SOLVED] Of Toothpaste and Tears: Looking for help with a convoluted query

[therefinedwerewolf]

Hey there. is my first time registering on a board for assistance, so please forgive me if I'm overeager here. Full disclosure: I'm relatively new to MySQL (as in 14 months of self-taught tomfoolery). Here's the lowdown-- I hope someone can help.

 

Background

I'm building a product selector for an e-commerce site, using PHP 5.25 and MySQL 5.0.51a. Let's say, for the sake of the example, that the site is selling toothpaste, and I want to let visitors select some pre-defined attributes of our various lines of toothpaste and present them with a list of the products that have all of those attributes.

 

 

Tables

Here are the tables I'm using.

 

[pre]table: products

sku  | title

-------------------------------

02000 | Super Spearmint

02004 | Wintergreen Supreme

03000 | Peppermint Power

03200 | BaconBot

03221 | Ghost Paste

06030 | UltraBoring

 

 

table: toothpaste_attrib

attrib_id | desc_en

-------------------------------

1        | spearmint flavour

2        | wintergreen flavour

3        | peppermint flavour

4        | bacon flavour

5        | has tartar control

6        | has brightener

7        | has cavity control

8        | has nanobots

9        | is green

10        | is blue

11        | is white

12        | is clear

 

 

table: toothpaste_lookup

ref_id | sku  | attrib_id

--------------------------------

1      | 03200 | 4

2      | 03200 | 8

3      | 03200 | 11

4      | 03221 | 2

5      | 03221 | 7

6      | 03221 | 12

7      | 02004 | 2

8      | 02004 | 7

9      | 02004 | 9

10    | 06030 | 3

11    | 06030 | 7

12    | 06030 | 11

...

 

[/pre]

 

 

The Query

Before creating the form that will build a custom query for the user, I wanted to enter a few queries directly to make sure it was working. In reality, I might be asking for up to five attributes at once, but in this example, I'm only asking for two:

 

SELECT 
products.sku,
products.title
FROM 
products
WHERE
toothpaste_lookup.sku = products.sku
AND
toothpaste_lookup.attrib_id = toothpaste_attrib.attrib_id
AND
(toothpaste_lookup.attrib_id = 2
OR
toothpaste_lookup.attrib_id = 7)
GROUP BY
products.sku
ORDER BY
products.sku
DESC

 

What I want to see in the results are all products that have both wintergreen flavour (attribute 2) and cavity control (attribute 7). In this case, two products:

 

[pre]sku  | title

-------------------------------

02004 | Wintergreen Supreme

03221 | Ghost Paste[/pre]

 

What I get is a list of all of the products that have either of those attributes:

 

[pre]sku  | title

-------------------------------

02004 | Wintergreen Supreme

03221 | Ghost Paste

06030 | UltraBoring[/pre]

 

That last result, UltraBoring, shouldn't be there, because it doesn't have attribute 2 set. It shouldn't be there, yet it is. Mocking me. I hate you, UltraBoring. I wish I had never made you up.

 

In looking at the syntax, I sort of understand why it's there, but I don't know how to revise the query to mandate the inclusion of both attributes for each product returned. I've tried replacing the OR with AND. I've tried moving the parentheses around. I've tried combinations of those changes. No luck. I've done some research and asked a few colleagues, and everything seems to point to doing inner joins with aliases, which is where I get lost and start wanting to lay down.

 

Can anyone shed some light on the situation? I'm a nub, ready to be schooled.

[fenway]

You'll need to count the number of matches and check it with a HAVING clause to filter out the others... and an IN() clause may prove helpful, too.

[therefinedwerewolf]

You're absolutely correct, fenway, thank you. Here is the updated query, which works a treat:

 

SELECT 
products.sku,
products.title
FROM 
products
WHERE
toothpaste_lookup.sku = products.sku
AND
toothpaste_lookup.attrib_id = toothpaste_attrib.attrib_id
AND
toothpaste_lookup.attrib_id IN (2,7)
GROUP BY
products.sku
HAVING 
        COUNT(*) = 2
ORDER BY
products.sku
DESC

 

My thanks to fenway and Guelphdad!