Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with a SQL Statement

Posted on 2009-05-18
4
Medium Priority
?
181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

721 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