?
Solved

SQL Staement

Posted on 2011-10-07
6
Medium Priority
?
241 Views
Last Modified: 2012-05-12
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 Product TableMain Filters table
 Main Filter 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?
0
Comment
Question by:harris9999
  • 3
  • 2
6 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 36930905
FIRST of all, why are you doing this:

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.
0
 
LVL 3

Author Comment

by:harris9999
ID: 36931248
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?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36931745
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
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 3

Author Comment

by:harris9999
ID: 36931770
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?
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 36931869
>>What is the best way to do it then, say if a product belongs to multiple categories?

The best would be to have separate, related tables.  So, to map products and categories, for example...

tblProducts
-------------------------------------------
ProductID (PK)
ProductName

tblCategories
-------------------------------------------
CategoryID (PK)
CategoryName

tblProductCategories
-------------------------------------------
ProductID (PK, FK)
CategoryID (PK, FK)

This is one example of what is meant by database normalization.

>>Is my query not possible with my existing setup?

Possible, yes, but excruciatingly painful.  Not to mention that future maintenance with your current design will also be excuciatingly painful.
0
 
LVL 3

Author Comment

by:harris9999
ID: 36931918
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question