Re: Use like clause more then once

From: Jeremy Smith <godtoall_at_hotmail.com>
Date: 29 May 2007 19:59:28 -0700
Message-ID: <1180493968.670010.225710@p77g2000hsh.googlegroups.com>

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. Received on Tue May 29 2007 - 21:59:28 CDT