Struggling with a Query
Posted: Tue Dec 15, 2009 4:21 am
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
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.
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
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 |
+------------+------------------+
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();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