Solved

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

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 107
TSQL mapping detailed records to group records 9 58
Query to Add Late Tolerance 10 76
Help with SQL - TOP 10 by date and by group 13 40
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…

789 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