SQL Staement
Having trouble getting my SQL statement right.
Have a products page and filters down the left hand side. Currently it works if 2 filters is selected on the left then it either of those filters are matched then the product is displayed.
I want it to be that if both filters are matched then the product is displayed.
Main Product Table
Main Filters table
Currently MY SQL Statement is (which shows product if either filter mathed) and works:
Select DISTINCT Main.ID, Main.* From Main INNER JOIN MainFilters ON Main.Id = MainFilters.MainId WHERE Available=1 AND (CategoryIds = '3' OR CategoryIds LIKE '3,%' OR CategoryIds LIKE '%, 3,%' OR CategoryIds LIKE '%, 3') AND (((FilterId=4) AND ( FilterValues = '9' OR FilterValues Like '%9,%' OR FilterValues Like '%, 9')) OR ((FilterId=9) AND ( FilterValues = '43' OR FilterValues Like '%43,%' OR FilterValues Like '%, 43'))) Order By DisOrder, Main.ID Desc, Title
But I am having trouble to get both match.
Tried this SQL but it doesn't work.
Select DISTINCT Main.ID, Main.* From Main INNER JOIN MainFilters ON Main.Id = MainFilters.MainId WHERE Available=1 AND (CategoryIds = '3' OR CategoryIds LIKE '3,%' OR CategoryIds LIKE '%, 3,%' OR CategoryIds LIKE '%, 3') AND (((FilterId=4) AND ( FilterValues = '9' OR FilterValues Like '%9,%' OR FilterValues Like '%, 9')) AND ((FilterId=9) AND ( FilterValues = '43' OR FilterValues Like '%43,%' OR FilterValues Like '%, 43'))) Order By DisOrder, Main.ID Desc, Title
How should I build my SQL Statement?
Have a products page and filters down the left hand side. Currently it works if 2 filters is selected on the left then it either of those filters are matched then the product is displayed.
I want it to be that if both filters are matched then the product is displayed.
Main Product Table
Main Filters tableCurrently MY SQL Statement is (which shows product if either filter mathed) and works:
Select DISTINCT Main.ID, Main.* From Main INNER JOIN MainFilters ON Main.Id = MainFilters.MainId WHERE Available=1 AND (CategoryIds = '3' OR CategoryIds LIKE '3,%' OR CategoryIds LIKE '%, 3,%' OR CategoryIds LIKE '%, 3') AND (((FilterId=4) AND ( FilterValues = '9' OR FilterValues Like '%9,%' OR FilterValues Like '%, 9')) OR ((FilterId=9) AND ( FilterValues = '43' OR FilterValues Like '%43,%' OR FilterValues Like '%, 43'))) Order By DisOrder, Main.ID Desc, Title
But I am having trouble to get both match.
Tried this SQL but it doesn't work.
Select DISTINCT Main.ID, Main.* From Main INNER JOIN MainFilters ON Main.Id = MainFilters.MainId WHERE Available=1 AND (CategoryIds = '3' OR CategoryIds LIKE '3,%' OR CategoryIds LIKE '%, 3,%' OR CategoryIds LIKE '%, 3') AND (((FilterId=4) AND ( FilterValues = '9' OR FilterValues Like '%9,%' OR FilterValues Like '%, 9')) AND ((FilterId=9) AND ( FilterValues = '43' OR FilterValues Like '%43,%' OR FilterValues Like '%, 43'))) Order By DisOrder, Main.ID Desc, Title
How should I build my SQL Statement?
ASKER
I have the Main ID there for the DISTINCT to work.
CategoryId's won't work like that as that is a text Field and in the image above has a number of images.
the problem occurs when there is 2 filters selected.
with just 1 it is:
AND (((FilterId=9) AND ( FilterValues = '43' OR FilterValues Like '%43,%' OR FilterValues Like '%, 43')))
But with 2 it is:
AND (((FilterId=4) AND ( FilterValues = '9' OR FilterValues Like '%9,%' OR FilterValues Like '%, 9')) AND ((FilterId=9) AND ( FilterValues = '43' OR FilterValues Like '%43,%' OR FilterValues Like '%, 43')))
When that 2nd AND is an OR it works when either condition is matched but when it is an AND when I want both conditions maths it doesn;t work. Is it because they are coming from the same table?
Do I need to use a sub query?
CategoryId's won't work like that as that is a text Field and in the image above has a number of images.
the problem occurs when there is 2 filters selected.
with just 1 it is:
AND (((FilterId=9) AND ( FilterValues = '43' OR FilterValues Like '%43,%' OR FilterValues Like '%, 43')))
But with 2 it is:
AND (((FilterId=4) AND ( FilterValues = '9' OR FilterValues Like '%9,%' OR FilterValues Like '%, 9')) AND ((FilterId=9) AND ( FilterValues = '43' OR FilterValues Like '%43,%' OR FilterValues Like '%, 43')))
When that 2nd AND is an OR it works when either condition is matched but when it is an AND when I want both conditions maths it doesn;t work. Is it because they are coming from the same table?
Do I need to use a sub query?
harris9999,
The very first thing you need to do is revamp that design. Trying to pack multiple values into the same column is just begging for trouble.
Patrick
The very first thing you need to do is revamp that design. Trying to pack multiple values into the same column is just begging for trouble.
Patrick
ASKER
What is the best way to do it then, say if a product belongs to multiple categories?
Is my query not possible with my existing setup?
Is my query not possible with my existing setup?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep that would have been better alright. Must do that in the future.
Though I'm at a stage with this project now where it would be easier to get this SQL statement working.
I have started using a sub query to try and pull the MainId from the filters table.
Though I'm at a stage with this project now where it would be easier to get this SQL statement working.
I have started using a sub query to try and pull the MainId from the filters table.
Main.ID, Main.* ?????
Don't you see that you are selecting ID TWICE?
You are better off with either select * from Main or Select ID, field2, field3...fieldN from...
ALso, instead of CategoryIds = '3' , etc, you could try CategoryIDs in('3','9','43').
I am assuming that by wrapping values in '' (single quotes), the CategoryIds is string, not integer.