CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-like
user992322
user992322

Reputation: 33

select query using multiple like conditions

Below is an existing ms sql server 2008 report query.

SELECT
    number, batchtype, customer, systemmonth, systemyear, entered, comment, totalpaid
FROM
    payhistory LEFT OUTER JOIN agency ON
        payhistory.SendingID = agency.agencyid      
WHERE
    payhistory.batchtype LIKE 'p%' AND
    payhistory.entered >= '2011-08-01 00:00:00.00' AND
    payhistory.entered <  '2011-08-15 00:00:00.00' AND
    payhistory.systemmonth = 8 AND
    payhistory.systemyear = 2011 AND
    payhistory.comment NOT LIKE 'Elit%'

Results will look like this:

number  batchtype   customer    systemmonth systemyear  entered     comment         totalpaid
6255756 PC      EMC1106     8       2011        12:00:00 AM DP From - NO CASH       33
5575317 PA      ERS002      8       2011        12:00:00 AM MO-0051381526 7/31      20
6227031 PA      FTS1104     8       2011        12:00:00 AM MO-10422682168 7/30     25
6232589 PC      FTS1104     8       2011        12:00:00 AM DP From - NO CASH       103
2548281 PC      WAP1001     8       2011        12:00:00 AM NCO DP $1,445.41        89.41
4544785 PCR     WAP1001     8       2011        12:00:00 AM NCO DP $1,445.41        39

What I am trying to do is modify the query that will exclude records where the customer is like 'FTS%' and 'EMC%' and batchtype = 'PC'. As you can see in the result set there are records where customer is like FTS% and batchtype = 'PA'. I would like to keep these records in the results. I would appreciate any ideas offered.

Upvotes: 3

Views: 81143

Answers (5)

Anki007
Anki007

Reputation: 411

$sql="select * from builder_property where builder_pro_name LIKE '%%' OR builder_pro_name LIKE '%za%' AND status='Active'";

This will return all the builder property name in table that will ends name like plaza or complex.

Upvotes: 3

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

When building complex where clauses it is a good idea to use parenthesis to keep everything straight. Also when using multiple NOT LIKE statements you have to combine all of the NOT LIKE conditions together using ORs and wrap them inside of a separate AND condition like this...


WHERE     
    (payhistory.batchtype LIKE 'p%')
AND (payhistory.entered >= '2011-08-01 00:00:00.00')
AND (payhistory.entered <  '2011-08-15 00:00:00.00')
AND (payhistory.systemmonth = 8 )
AND (payhistory.systemyear = 2011)
AND ( // BEGIN NOT LIKE CODE

    (payhistory.comment NOT LIKE 'Elit%') 

OR  (
    (payhistory.customer NOT LIKE 'EMC%') AND 
    (payhistory.batchtype = 'PC')
    ) 

OR  (
    (payhistory.customer NOT LIKE 'FTS%') AND 
    (payhistory.batchtype = 'PC')
    )

    ) //END NOT LIKE CODE

Upvotes: 0

tedders
tedders

Reputation: 1022

Add this condition to the WHERE clause:

NOT((customer LIKE 'FTS%' OR customer LIKE 'EMC%') AND batchtype='PC')

Assuming your other results are OK and you just want to filter those out, the whole query would be

SELECT
    number, batchtype, customer, systemmonth, systemyear, entered, comment, totalpaid
FROM
    payhistory 
    LEFT OUTER JOIN agency ON
        payhistory.SendingID = agency.agencyid      
WHERE
    payhistory.batchtype LIKE 'p%' AND
    payhistory.entered >= '2011-08-01 00:00:00.00' AND
    payhistory.entered <  '2011-08-15 00:00:00.00' AND
    payhistory.systemmonth = 8 AND
    payhistory.systemyear = 2011 AND
    payhistory.comment NOT LIKE 'Elit%' AND
    NOT((payhistory.customer LIKE 'FTS%' OR payhistory.customer LIKE 'EMC%') AND payhistory.batchtype='PC')

Hope that works for you.

Upvotes: 0

Geoff
Geoff

Reputation: 8850

Your query contains a mix of upper and lower string comparison targets. As far as I'm aware, SQL Server is not by default case-sensitive; is it possible this is what is tripping your query up? Check collation per this answer.

EDIT: Based on your updated question, can you not just use an AND clause that uses a NOT on the front? In other words, add a 'AND not (x)' clause, where 'x' is the conditions that define the records you want to exclude? You'd need to nest the customer test, because it's an OR. e.g.:

... payhistory.comment NOT LIKE 'Elit%'
AND not ((customer like 'FTS%' or customer like 'EMC%') AND batchtype = 'PC')

As a side note, I believe that a LIKE clause may imply an inefficient table scan in some (but not all) cases, so if this query will be used in a performance-sensitive role you may want to check the query plan, and optimise the table to suit.

Upvotes: 4

Bala
Bala

Reputation: 4547

It can be because your sever might be case sensitive. In that case, below query would work. 

SELECT
table1.number, table1.btype, table1.cust, table1.comment, table2.ACode
FROM
table1 LEFT OUTER JOIN table2 ON table1.1ID = table2.2ID
WHERE
lower(table1.btype) LIKE 'p%' AND
lower(table1.comment) NOT LIKE 'yyy%' AND
lower(table1.cust) NOT LIKE 'abc%' AND
lower(table1.cust) NOT LIKE 'xyz%' AND
lower(table1.btype) <> 'pc'

Upvotes: 0

Related Questions