Solved

Using  'And'   to get result of matching data in sql table

Posted on 2011-03-08
4
272 Views
Last Modified: 2012-05-11
I have a sql table with two records. Please look at the attached Image.

I want to be able to test every field for a particular value. If I run the query below

select value1,value2,value3 from tester2
where value1 like  '%'+ 'aaa' + '%' or value2 like  '%'+ 'aaa' + '%' or value3 like  '%'+ 'aaa' + '%'
And (value1 like  '%'+ 'bbb' + '%' or value2 like  '%'+ 'bbb' + '%' or value3 like  '%'+ 'bbb' + '%')

My query returns

   value1   value2    value3

1  aaa        null          null


This is wrong because  I only have one entry of aaa and another of bbb. I dont have a situation where both aaa and bbb exists for a record. How can I correct this?
If i use or instead of the And, it works very well.  

sample sql table
0
Comment
Question by:Sirdots
[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
4 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35073339

You must use OR

using AND implies that the a filed must satisfy both conditions ie (id like 'aaa' and id like 'bbb') which is not possible

The only time and would be true in your case is if you had a record with id 'aaabbb'
Then in this case both conditions match this value Like %aaa% and Like %bbb%
0
 

Author Comment

by:Sirdots
ID: 35073364
I want to be able to use OR and And. How can i do this? OR works very well for me.
0
 
LVL 9

Accepted Solution

by:
AriMc earned 500 total points
ID: 35073368
First, you probably meant to enclose the first group of tests in parenthesis.
Second, why not use '%aaa%' instead of '%'+'aaa'+'%'?

Try this:
select value1,value2,value3 from tester2
where (value1 like '%aaa%' or value2 like '%aaa%' or value3 like  '%aaa%')
And (value1 like '%bbb%' or value2 like '%bbb%' or value3 like  '%bbb%')

Open in new window

0
 

Author Closing Comment

by:Sirdots
ID: 35073440
Thanks. Very helpful and works for me.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I get this column in my query? 2 63
Gettg error - Please help Msg 252, Level 16, State 1, Line 1 3 37
SQL Error - Query 6 55
calculate running total 8 51
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

752 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