Solved

Help with a SQL Statement

Posted on 2009-05-18
4
175 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 100 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

832 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