Solved

Using Like

Posted on 2009-05-13
9
179 Views
Last Modified: 2012-05-06
I'm trying to use "Like" like below but this only looks for exact matches. How can I do this?

Note that F_Alert table has no keys related to the other table.
select * from f_alert where 
   criteria like (select Addressee from LAddress lc
                      inner join customeraddress mca on mca.addressid=lc.id  where mca.customerid = '123')

Open in new window

0
Comment
Question by:Camillia
[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
9 Comments
 
LVL 10

Expert Comment

by:Espavo
ID: 24376717
Try this:
select * from f_alert where 
   criteria like '%(select Addressee from LAddress lc
                      inner join customeraddress mca on mca.addressid=lc.id  where mca.customerid = '123')%'

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24376720

select *
from f_alert f
where exists
(select null
from LAddress lc
inner join customeraddress mca on mca.addressid=lc.id
where mca.customerid = '123' AND f.criteria like '%'+Addressee+'%')
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24377358
Try the following two options.  Using LIKE is bad enough without compounding things with an EXISTS. ;-)

Option 1:
 
SELECT F.* 
FROM f_alert F
INNER JOIN LAddress A
ON F.criteria = A.Addressee
INNER JOIN  customeraddress C
ON C.addressid=A.id  
AND C.customerid LIKE '%' + '123' + '%';
 
Option 2:
SELECT F.* 
FROM f_alert F
INNER JOIN LAddress A
ON F.criteria = A.Addressee
INNER JOIN  customeraddress C
ON C.addressid=A.id  
WHERE C.customerid LIKE '%' + '123' + '%';

Open in new window

0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 7

Author Comment

by:Camillia
ID: 24377473
let me see. Thanks. Yeah, like is not good.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24377550
LIKE is Not A Good THing . . . unless it is the only way to get there from here. ;-)
However, when you use LIKE, you just have to be aware that you have just agreed to have potentially very poor performance and, if you are playing with enough data, time outs. ;-)
Where I work, there is an application that may wind up using up to 5 instances of LIKE '%whatever%' on tables with millions of rows . . . and they keep asking me to provide indexes to solve their performance issues. ;-)
0
 
LVL 7

Author Comment

by:Camillia
ID: 24377648
Fraud_alert table has a row with column =330.  LocAddress has a column=33092

It should bring back that "330" row but it doesnt:

select * 
from fraud_alert f 
where exists 
(select null 
from LocAddress lc 
inner join memcustomeraddress mca on mca.addressid=lc.id 
where mca.customerid = '123' AND 
(f.criteria like '%' + PostalCode + '%' OR f.criteria like '%'+Addressee+'%' OR f.criteria like '%'+ Address1 + '%' )
 
)

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24378271
You've got your LIKEs cross wired.  (And I still done't like the EXISTS approach but I don't have time to alter that part. ;-)
select * 
from fraud_alert f 
where exists 
(select null 
from LocAddress lc 
inner join memcustomeraddress mca on mca.addressid=lc.id 
where mca.customerid = '123' AND 
(PostalCode like '%' + f.criteria + '%' OR Addressee like '%'+f.criteria+'%' OR Address1 like '%'+ f.criteria + '%' )
 
)

Open in new window

0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 24378322
On second thought, it didn't take too long: ;-)
SELECT f.* 
FROM fraud_alert f 
INNER JOIN 
(
 SELECT PostalCode,
        Addressee,
        Address1 
FROM LocAddress lc 
INNER JOIN memcustomeraddress mca 
   ON mca.addressid=lc.id 
WHERE  mca.customerid = '123'
) Z
ON (Z.PostalCode like '%' + f.criteria + '%' ) OR 
   (Z.Addressee like '%'+f.criteria+'%' ) OR 
   (Address1 like '%'+ f.criteria + '%' );
 

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 24378484
8080: your last one worked. Let me try one more thing and will post back
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

630 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