?
Solved

Help with a SQL Statement

Posted on 2009-05-18
4
Medium Priority
?
183 Views
Last Modified: 2012-05-07
I have the following tables with the following fields:

Username
Program1
Program1Allottment
Program2
Program2Allottment
Program3
Program3Allottment
Program4
Program4Allottment

I need to run a query that will pull a list of users if a certain search term is found in either the Program1, Program2, Program3 or Program4 fields.

Help?
0
Comment
Question by:SherryG
  • 2
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24415817
try using a union statement

declare @Searchterm varchar(100)

SELECT users from urTable where Program1 like '%'+@SearhTerm+'%'
UNION
SELECT users from urTable where Program2 like '%'+@SearhTerm+'%'
UNION
SELECT users from urTable where Program3 like '%'+@SearhTerm+'%'
UNION
SELECT users from urTable where Program4 like '%'+@SearhTerm+'%'
0
 
LVL 4

Accepted Solution

by:
dublingills earned 400 total points
ID: 24415959
Similar suggestion except a union is unnecessary:

declare @Searchterm varchar(100)

SELECT users
FROM table
WHERE Program1 LIKE '%'+@SearhTerm+'%'
OR Program2 LIKE '%'+@SearhTerm+'%'
OR Program3 LIKE '%'+@SearhTerm+'%'
OR Program4 LIKE '%'+@SearhTerm+'%'
      
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24415972
Better to use a UNION rather than OR, as UNION uses a better plan than one with 'OR;
0
 
LVL 4

Expert Comment

by:dublingills
ID: 24416104
aneeshattingal I certainly won't argue with you regarding the plan but in my own experience I can't agree.

Either way, from the OP's perspective either solution will provide the required answer.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 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