Page 1 of 1

Struggling with a Query

Posted: Tue Dec 15, 2009 4:21 am
by Turv
Hey.

Okay, I'll first tell you what i'm trying to do so you can get a basic idea. I'm creating a 'Bra Finder' for a lingerie website, This will be a series of drop down boxes such as Bra Size, Colour, etc. As an example you could select Bra Size of 32D, and it will give you a list of products in that bra size, you can then select colour of Black and it will give you a list of products that are available in 32D and Black.

Okay, so first of all i'll show you my Database structure

Code: Select all

 
# Table: filter
+-----------+-------------------+-------------------+
| filter_id | filter_group_name | filter_sort_order |
+-----------+-------------------+-------------------+
|        1  | Bra Size          |                 1 |
|        2  | Colour           |                 2 |
+-----------+-------------------+-------------------+
 

Code: Select all

 
# Table: filter_options
+------------------+-----------+--------------------+-------------------+
| filter_option_id | filter_id | filter_option_name | option_sort_order |
+------------------+-----------+--------------------+-------------------+
|              1   |        1  | 32A                |                 1 |
|              2   |        2  | Black              |                 2 |
+------------------+-----------+--------------------+-------------------+
 

Code: Select all

 
# Table: product_to_filter
+------------+------------------+
| product_id | filter_option_id |
+------------+------------------+
|        100 |                1 |
|        100 |                2 |
|        101 |                1 |
+------------+------------------+
 
Right, To sumarise the above, I have my filters table which are the filter groups such as Bra Size, and Colour. Then i have the filter options, these can be Black, Red, White for colours or 30B, 32C etc for Bra Sizes. I then finally have my product_to_filter table. This is a many to many relationship so multiple products can have multiple filter options.

From the table above you can see that Product #100 has the Filter of Bra Size 32A and Colour Black, Where as Product #101 only has the option of Bra Size 32A.

On the website, when someone wants to 'filter' the products, and they select for example the Bra Size of 32A, It will post the Option ID (From the filter_options table) to another page. So as an example a user selects a filter of Bra Size 32A (Option ID# 1) and Colour Black (Option ID#2) I then perform the following query.

Code: Select all

SELECT *, pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock FROM product_to_filter ptf 
                LEFT JOIN product p ON ptf.product_id = p.product_id 
                LEFT JOIN product_description pd ON (p.product_id = pd.product_id) 
                LEFT JOIN manufacturer m ON (p.manufacturer_id = m.manufacturer_id) 
                LEFT JOIN stock_status ss ON (p.stock_status_id = ss.stock_status_id) 
                WHERE ptf.filter_option_id IN (1,2) AND p.status = '1' AND p.date_available <= NOW();
This basically queries the product_to_filters table to find all the products that have the Id of Either 1 or 2.

Now The problem with the above is, it will include 3 Results, It will include Product #100 Twice, and Product #101 Once. This is because it matches that Product #100 has the filter id of both 1 and 2, and Product #101 has the id of 1.

What i need it to do is actually filter it so that if you select both Bra Size of 32A and Colour Black it will only get the records that have BOTH Id of 1 and 2, and not include any results where only one of them is present.

I know i am in the wrong by using the IN Command, but can anyone help me re-write the query above to work?

Many Thanks

Re: Struggling with a Query

Posted: Tue Dec 15, 2009 7:32 am
by VladSun
Choose one:

UNTESTED!!!
[sql]SELECT *FROM product_to_filter ptf INNER JOIN product p1 ON ptf.product_id = p1.product_id AND ptf.filter_option_id = 1INNER JOIN product p2 ON ptf.product_id = p2.product_id AND ptf.filter_option_id = 2[/sql]

[sql]SELECT *FROM productWHERE     EXISTS(SELECT FROM product_to_filter WHERE ptf.product_id = product.product_id AND ptf.filter_option_id = 1)    AND    EXISTS(SELECT FROM product_to_filter WHERE ptf.product_id = product.product_id AND ptf.filter_option_id = 2) [/sql]

[sql]SELECT     DISTINCT product.id, count(*) AS _countFROM     productLEFT JOIN     product_to_filter ptf WHERE ptf.product_id = product.product_id)WHERE     ptf.filter_option_id IN (1,2)GROUP BY     product.idHAVING     _count = 2[/sql]

PS: Try not to use SELECT *
PPS: Please, use [ sql ] [/ sql ] BBcode tags to surround the SQL code you post here.

Re: Struggling with a Query

Posted: Wed Dec 16, 2009 8:41 am
by Turv
VladSun wrote: [sql]SELECT     DISTINCT product.id, count(*) AS _countFROM     productLEFT JOIN     product_to_filter ptf WHERE ptf.product_id = product.product_id)WHERE     ptf.filter_option_id IN (1,2)GROUP BY     product.idHAVING     _count = 2[/sql]
Many Thanks VladSun, I've never worked with the Group By / Having command but that query worked brilliantly.