Solved

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

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

Independent Software Vendors: 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

Suggested Solutions

Title # Comments Views Activity
SQL Select - Finding chars in a column 2 71
sql query help 2 62
Caste datetime 2 69
How to resolve SQL Server DB deadlock which makes my application hangs ? 6 59
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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 …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

756 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