Solved

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

Posted on 2011-03-08
4
236 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
  • 2
4 Comments
 
LVL 32

Expert Comment

by:ewangoya
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 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

11 Experts available now in Live!

Get 1:1 Help Now