Re: Use like clause more then once

On May 30, 6:59 am, Jeremy Smith <godto..._at_hotmail.com> wrote:
> I need the and in the statement and I'm sorry for not providing all of
> the info.
>
> services table would aslo have this insert.
>
> SQL> insert into services values (1, 'TSM');
>
> 1 row created.
>
> SQL> select * from services;
>
> ID DISPLAY_NAME
> ---------- --------------------------------------------------
> 1 MICROSOFT EXCHANGE
> 1 TSM
> 2 TSM
> 3 MICROSOFT EXCHANGE
> 4 SOMETHING ELSE
>
> Then run
>
> SQL> select id, name
> 2 from servers
> 3 where id IN (select id
> 4 from services
> 5 where display_name like '%MICROSOFT EXCHANGE')
> 6 and id in (select id
> 7 from services
> 8 where display_name like '%TSM%')
> 9*
> SQL> /
>
> and you get
>
> ID NAME
> ---------- --------------------------------------------------
> 1 server1
>
> But if I did my update qury to reduce scanning the services table
> twice.
>
> SQL> select s.id, s.name
> 2 from servers s, services sr
> 3 where s.id = sr.id
> 4 and sr.display_name like '%MICROSOFT EXCHANGE'
> 5* and sr.display_name like '%TSM%'
> SQL> /
>
> I get
>
> no rows selected
>
> So if I do a LIKE clause with an AND it comes back no rows, when the
> condition exists.
>
> Thanks for any help with this.

When you AND the two LIKEs, Oracle tries to find rows where BOTH substrings exist in display_name, hence you receive no rows. You need to use something like this:

select server_id, server_name
  from servers
 where status_id = 1
   and server_id in (select server_id from services

               where (svc_display_name like '%MICROSOFT EXCHANGE%'
                  or  svc_display_name like '%TSM%')
               group by server_id having count(*) > 1)

The subquery groups rows by ID and retains only those IDs for which both matches were found. If you need 3 matches, you will group by having count() > 2, for 4 matches it will be > 3, etc.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed May 30 2007 - 06:31:56 CDT