Solved

Help with a SQL Statement

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now