Please forgive the long working of this query - I’m struggling to put in to words what I want to achieve, hopefully the description below gives the gist.
I’m using a separate table to store relationships between two other tables.
I have one table that stores a list of products and another table that stores a list of product categories. Products are assigned to one (or many) categories.
The product table looks like this:
table name: tblproducts
prodId INT (auto increment)
prodTitle VARCHAR 100
The category table looks like this:
table name: tblprodcategories
catId INT (auto increment)
catTitle VARCHAR 100
The relationship table looks like this:
table name: tblcatrelations
relId INT (auto increment)
relcatId INT (stores the catId from the tblprodcategories table)
relprodId INT (stores the prodId from the tblproducts table)
So my relationship table data looks like:
relId relcatId relprodId
1 2 2
2 2 3
3 2 5
4 3 1
5 4 1
6 4 2
…so that data shows that product ID 2 is in both category (ID) 2 and 4, product ID 3 and 5 are both only in category (ID) 2. Product ID 1 is in both categories 3 and 4.
I use the following MySQL Select Statement and it works fine (where the ? is a passed variable containing the catId):
"SELECT prodId, prodTitle FROM tblcatrelations LEFT JOIN tblproducts ON prodId = relprodId WHERE relcatId = ? ORDER BY prodTitle ASC"
This all works fine, but now I need to add another level of filtering: “sub categories”.
The sub category table looks like this:
table name: tblprodsubcategories
subId INT (auto increment)
subTitle VARCHAR 100
I’ve added another field to tblcatrelations:
table name: tblcatrelations
relId INT (auto increment)
relcatId INT (stores the catId from the tblprodcategories table)
relprodId INT (stores the prodId from the tblproducts table)
relsubId INT (stores the subId from the tblprodsubcategories table)
So now my relationship table data looks like:
relId relcatId relprodId relsubId
1 2 2 0
2 2 3 0
3 2 5 0
4 3 1 0
5 4 1 0
6 4 2 0
7 2 0 1
8 2 0 2
9 3 0 3
10 0 2 1
11 0 1 1
the first 6 (relId 6) entries are the same. Then relId 7 shows that sub category 1 and 2 are related to category 2 and sub category 3 is related to category 3. Then relId 10 shows that product 2 and 1 are related to sub category 1.
What I need my MySQL Select statement to do is list the results so when both a category and sub category variable is passed (i.e. ?catId=2&subId=1) it searches for products where the category ID (relcatId) matches the querystring variable “catId” and the sub category ID (relsubId) matches the querystring variable “subId” but only lists products which match both queries. For example:
?catId=2&subId=1
would just match product ID 2 (products 2, 3 and 5 match the “catId” 2 and products 2 and 1 match the “subId” 1 but only product 2 is in both lists/matches.
I’d love to give you an example of what I’ve tried but I don’t even know where to begin with this query - basically it’s making two searches and producing results of only items/products that are in both searches.
Hope someone can understand what I mean and help.
Many thanks.