Advanced SQL Statement

Posted on 2011-10-07
Medium Priority
Last Modified: 2012-05-12
I am having trouble trying to create the correct SQL Statement to pull the required records from the table in the image below.
Filters TableThere are lots of records in the table, but I want to pull the MainId from the table when a couple of conditions are met.
I need something like:
Select MainID From MainFilters Where (FilterId=4 AND FilterValues='9') AND (FilterId=9 AND FilterValues='43')
but only that doesn't appear to work? is it because both conditions are in the same record?

Question by:harris9999
  • 3
  • 2
LVL 84

Expert Comment

by:Dave Baldwin
ID: 36933718
It needs to be:

Select MainID From MainFilters Where (FilterId=4 AND FilterValues='9') OR (FilterId=9 AND FilterValues='43')

OR will select either condition.  Without OR, you can't match two different values in the same column and row.  You can't have both '4' and '9' as FilterId values at the same time.

Author Comment

ID: 36933848
I need both conditions met.  
Is that not possible in any way?
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36933859
then you need to do something like
Select f.MainID 
From MainFilters f
Where (f.FilterId=4 AND f.FilterValues='9')
 AND EXISTS( SELECT NULL FROM MainFilters o WHERE (o.FilterId=9 AND o.FilterValues='43' AND o.MainID = f.MainID ) )

Open in new window

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!


Author Comment

ID: 36933941
That looks like it works alright.
And can that be extended if 3 or more conditions needed met?
FilterId=4 AND FilterId=9
FilterId=9 AND FilterId=43
FilterId=5 AND FilterId=26
FilterId=3 AND FilterId=3
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36934054
for every other condition you add another AND EXISTS() condition ...

Author Comment

ID: 36934063
Thanks for your help!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

809 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