CODEWITHSUNDEEP
sqlsql-server
eMRe
eMRe

Reputation: 3247

How to use multiple "like" and "and"

I cant get this sql query work the way i want to. I am trying to match a record using "like" and "and" but i dont get any result. When i change it to "or" I get the result.

Why do i not get any results when i use and?

SELECT   a.MOD_REQ_NUM,
         a.REFERENCE_CODE,
         a.REFERENCE_VALUE,
         a.ACTIVE_DETAIL,
         b.REQUESTOR_WWID,
         b.REQUEST_DATE
 from DB_MOD_REQ_DETAILS a, DB_MOD_REQ_HEADER b
 where a.MOD_REQ_NUM=b.MOD_REQ_NUM
 and a.ACTIVE_DETAIL='Y' 
 and b.ACTIVE_HEADER='Y'
 ----------------------------------------------------------
 and 
    (a.REFERENCE_VALUE like '%43598%' and a.REFERENCE_CODE = 2)
 and 
    (a.REFERENCE_VALUE like '%3694894%' and a.REFERENCE_CODE = 4)

This is the screenshot when i use "or"

enter image description here

Update

I want to be able to search the a.REFERENCE_VALUE only in the given a.REFERENCE_CODE. I have got form fields and C# foreach loop. Every field has got its own REFERENCE_CODE. if user is searching for 1 field, i want to do:

(a.REFERENCE_VALUE like '%43598%' and a.REFERENCE_CODE = 2)

If user is searching more than 1 field, i want to match :

a.REFERENCE_VALUE like '%43598%' and a.REFERENCE_CODE = 2
and
a.REFERENCE_VALUE like '%3694894%' and a.REFERENCE_CODE = 4

this is the screen shot of the query with all the data enter image description here

Upvotes: 0

Views: 308

Answers (3)

cooltea
cooltea

Reputation: 1113

From your question and your comment to jmcilhinney's answer the SQL statement you want is:

SELECT   a.MOD_REQ_NUM,
         a.REFERENCE_CODE,
         a.REFERENCE_VALUE,
         a.ACTIVE_DETAIL,
         b.REQUESTOR_WWID,
         b.REQUEST_DATE
FROM DB_MOD_REQ_DETAILS a, DB_MOD_REQ_HEADER b
WHERE a.MOD_REQ_NUM=b.MOD_REQ_NUM
  AND a.ACTIVE_DETAIL='Y' 
  AND b.ACTIVE_HEADER='Y'
----------------------------------------------------------
  AND 
  (
      (a.REFERENCE_VALUE like '%43598%' and a.REFERENCE_CODE = 2)
      OR
      (a.REFERENCE_VALUE like '%3694894%' and a.REFERENCE_CODE = 4)
  )

Notice the parentheses to the last clause.

Here is why:

  1. Your first statement has AND operators everywhere. This results in having a conjunction of other clauses (a.MOD_REQ_NUM=b.MOD_REQ_NUM AND a.ACTIVE_DETAIL='Y' AND b.ACTIVE_HEADER='Y' AND a.REFERENCE_VALUE like '%43598%' AND a.REFERENCE_VALUE like '%3694894%') as well as these: a.REFERENCE_CODE = 4 AND a.REFERENCE_CODE = 2 which is always false. Therefore you will get no result from this SQL statement.

  2. Your second statement is close to what you want but not quite it. As Luuan pointed out the parentheses are required: A AND B AND C OR D is not the same as A AND B AND (C OR D). See http://en.wikipedia.org/wiki/Associative_property for the long explanation.

EDIT: the piece of software you have provided in a comment to your question is the source of the problem as it does not generate the SQL statement that you need (and which both jmcilhinney and Beatles1692 tried to provide).

First replace the following line in GetParamDataSet

strCond = " a.REFERENCE_VALUE LIKE'%" + item.Substring(item.IndexOf('=') + 1).Trim() + "%'" +
                                " and a.REFERENCE_CODE= " + id;

with

strCond = " (a.REFERENCE_VALUE LIKE'%" + item.Substring(item.IndexOf('=') + 1).Trim() + "%'" +
                                " and a.REFERENCE_CODE= " + id + ")";

Notice the parentheses!

Second replace the main block in GetDataValue(List<string> conditions) with

string strSQL = "SELECT a.MOD_REQ_NUM, " +
                " a.REFERENCE_CODE, " +
                " a.REFERENCE_VALUE, " +
                " a.ACTIVE_DETAIL, " +
                " b.REQUESTOR_WWID, " +
                " b.REQUEST_DATE " +
                " from DB_MOD_REQ_DETAILS a, DB_MOD_REQ_HEADER b " +
                " where a.MOD_REQ_NUM=b.MOD_REQ_NUM " +
                " and a.ACTIVE_DETAIL='Y' and b.ACTIVE_HEADER='Y' ";

-- construct the conjunction of "(a.REFERENCE_VALUE LIKE '%%' and a.REFERENCE_CODE = %)"
string referenceCodeValueCondition = string.Join<string>( " OR ", conditions );

-- append the conjunction block to the other conditions
if (! string.IsNullOrEmpty( referenceCodeValueCondition ))
{
    strSQL = strSQL + " AND (" + referenceCodeValueCondition + ")";
}

Upvotes: 1

Beatles1692
Beatles1692

Reputation: 5320

(a.REFERENCE_VALUE like '%43598%' and a.REFERENCE_CODE = 2) and (a.REFERENCE_VALUE like '%3694894%' and a.REFERENCE_CODE = 4)

This means that both conditions should be true at the same time which in this scenario (according to your result) is not the case. When you change it to :

(a.REFERENCE_VALUE like '%43598%' and a.REFERENCE_CODE = 2) or(a.REFERENCE_VALUE like '%3694894%' and a.REFERENCE_CODE = 4)

It means that the whole statement considered to be true if any of these conditions are true and then you can see both of them in your result. The question is what you really want to do ?

Upvotes: 1

jmcilhinney
jmcilhinney

Reputation: 54417

This part doesn't make sense:

and 
    (a.REFERENCE_VALUE like '%43598%' and a.REFERENCE_CODE = 2)
and 
    (a.REFERENCE_VALUE like '%3694894%' and a.REFERENCE_CODE = 4)

How can REFERENCE_CODE ever be 2 and 4? At the very least, there should be an OR between those last two sets of conditions, i.e.

and 
    ((a.REFERENCE_VALUE like '%43598%' and a.REFERENCE_CODE = 2)
or
    (a.REFERENCE_VALUE like '%3694894%' and a.REFERENCE_CODE = 4))

You may or may not need to change other AND operators to OR as well.

Upvotes: 5

Related Questions