I am trying to create a system that will select candidates for a job
based on certain criteria (i.e. Supperted States)

The candidates are allowed to choose up to 5 supported states. The
problem comes when creating the query to pull the candidates out.

I can get it to work with only one supported state, no problem. But I
have no idea how to tell the DB to look through SupportedState1 OR
SupportedState2 OR SupportedState3 OR SupportedState4 OR
SupportedState5 to find the particular state that the job is in.

Does an OR operator exist; or at least some mechanism for achieving
this? Maybe there is a smarter way to implement this instead of 5
seperate fields for the supported states?

Thanks so much,
Alex

Alex (iamalex84@gmail.com) writes:
> I am trying to create a system that will select candidates for a job
> based on certain criteria (i.e. Supperted States)
>
> The candidates are allowed to choose up to 5 supported states. The
> problem comes when creating the query to pull the candidates out.
>
> I can get it to work with only one supported state, no problem. But I
> have no idea how to tell the DB to look through SupportedState1 OR
> SupportedState2 OR SupportedState3 OR SupportedState4 OR
> SupportedState5 to find the particular state that the job is in.
>
> Does an OR operator exist; or at least some mechanism for achieving
> this? Maybe there is a smarter way to implement this instead of 5
> seperate fields for the supported states?

There is an OR operator. But I have no clue how you should write the query
since I don't know your tables.

But if you have five columns for supported states, and you have the
possibility to redesign, do so. Make "supported states" table instead.
Then you may find that there is no reason to have a limit on five states,
at least not from the database point of view.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Redesign your db, i.e instead of a table with the 5 supported states
columns , set up a lookup table , and then a table with 2. columns i.e
candidateId | SupportedStaeID |

--
----
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________


"Alex" <iamalex84@gmail.com> wrote in message
news:1151703920.810636.228800@75g2000cwc.googlegro ups.com...
> I am trying to create a system that will select candidates for a job
> based on certain criteria (i.e. Supperted States)
>
> The candidates are allowed to choose up to 5 supported states. The
> problem comes when creating the query to pull the candidates out.
>
> I can get it to work with only one supported state, no problem. But I
> have no idea how to tell the DB to look through SupportedState1 OR
> SupportedState2 OR SupportedState3 OR SupportedState4 OR
> SupportedState5 to find the particular state that the job is in.
>
> Does an OR operator exist; or at least some mechanism for achieving
> this? Maybe there is a smarter way to implement this instead of 5
> seperate fields for the supported states?
>
> Thanks so much,
> Alex
>

The WHERE clause does recognize the OR operator, as in:

WHERE SupportedState1 = 'CA' OR SupportedState2 = 'WA'

Like the others pointed out though, what you should do is look at
re-designing these tables. Ideally you should have a separate "supported
state" table related to the candidates table by the candidate table primary
key. This turns your queries into a simple inner join without all the
explicit "OR" logic, and also allows you to store as many states per
candidate as you wish. It also helps enforce referential integrity. So if
a candidate only has one or two supported states you won't waste all that
extra time and programming logic trying to determine this. Also if your
requirements change in the future, like if the number of supported states
suddenly jumps up to 10 per candidate, you won't have to re-design all of
your tables and queries.