• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Advanced SQL Statement

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?

0
harris9999
Asked:
harris9999
  • 3
  • 2
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
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.
0
 
harris9999Author Commented:
I need both conditions met.  
Is that not possible in any way?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
harris9999Author Commented:
That looks like it works alright.
And can that be extended if 3 or more conditions needed met?
e.g.
FilterId=4 AND FilterId=9
FilterId=9 AND FilterId=43
FilterId=5 AND FilterId=26
FilterId=3 AND FilterId=3
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for every other condition you add another AND EXISTS() condition ...
0
 
harris9999Author Commented:
Super
Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now