Solved

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

Posted on 2011-03-08
4
243 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

948 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