how to make a select statment to search according to user needed ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

i have 2 table ::

*1st- applicants ::
(id , name)
*2nd- Interview_Schedule ::
( work_flow_id , interview_district , Job_ID , app_id)

*relation ::

(Interview_Schedule INNER JOIN applicants ON Interview_Schedule.app_id = applicants.id)

*WEBFORM ::

i have 3 dropdownlists to enter
( work_flow_id ) and (interview_district) and (Job_ID)
but the user can also enter only one of the three parameters
or two so its not required to enter the three values

# note ::

this is the select statment if the user must enter the three values

SELECT        applicants.name
FROM          Interview_Schedule INNER JOIN
              applicants ON Interview_Schedule.app_id = applicants.id
WHERE        Interview_Schedule.work_flow_id=@worflowid AND Interview_Schedule.interview_district=@distid AND Interview_Schedule.Job_ID=@jobid

# I NEED ::

how to make a select statment to search for applicants.name
according to user needed ? the where condition ????

Posted 28-Feb-11 21:23pm

MohammedSabry

Add a Solution

Comments

Albin Abel 1-Mar-11 4:31am

if one either one condition satisfy your result then why you want to check three condition. Use OR instead of AND in the where condition?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Answer 1 · 2011-02-28T21:59:00

the user can also enter only one of the three parameters or two so its not required to enter the three values
This UI design simply suggests that the values are not uniquely linked and you can get one or more results if you select the search criteria.

When, this is the case, you need to:
1. Change the query with LIKE instead of '='.
2. Make sure you put a '%' append to the search criteria values before assigning it to the parameters of query.
Thus, if you give 'Work_Flow_ID' = 3, then put @workflowid = "%3%";
=> if you select nothing for JOB_ID, then it will pass @jobid= "%%";

SQL

SELECT        
      applicants.name
FROM          
      Interview_Schedule 
INNER JOIN
      applicants ON Interview_Schedule.app_id = applicants.id
WHERE        
     Interview_Schedule.work_flow_id LIKE @worflowid 
AND 
     Interview_Schedule.interview_district LIKE @distid 
AND 
     Interview_Schedule.Job_ID LIKE @jobid

P.S.: Above changes does not ensure you a single unique record. In order to make that sure, you need to make atleast one field mandatory.