Re: Use like clause more then once

On May 30, 3: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.

Ah - thats because the AND clauses are both applied to the same row, and there are no rows like 'EXCHANGE ...' and 'TSM'.

Are you trying to find all servers where the same server has an EXCHANGE service AND a TSM service? Received on Wed May 30 2007 - 06:31:08 CDT