Solved

Help with a SQL Statement

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

18 Experts available now in Live!

Get 1:1 Help Now