Re: How to find out common records with Where clause
Date: Sat, 10 Jan 2009 06:02:01 -0800 (PST)
Message-ID: <35a31d5e-5ab2-416f-8bcf-5d2119c2c6b3_at_f40g2000pri.googlegroups.com>
On Jan 9, 10:57 am, ddf <orat..._at_msn.com> wrote:
snip
> SQL> -- Use the above queries to drive the final
> SQL> -- query and avoid using LIKE
> SQL> --
> SQL> with rel_loc as (
> 2 select empname, case when instr(address, '-') > 4 then
> substr(address, 1, instr(address,'-')-1)
> 3 when instr(address, '-') < 5 then
> substr(address, instr(address, '-')+1) end addr
> 4 from visitor
> 5 )
> 6 select empname
> 7 from rel_loc
> 8 where addr in ('Pune','Mumbai','Delhi')
> 9 group by empname
> 10 having count(*) = (select count(distinct addr) from rel_loc);
The preferred approach in this news group when posters submit questions that are obviously homework of some kind is to ask them to show what work they have done already.
> David Fitzjarrell
Kind of a lot of work here to provide something without any idea if what the OP provided is anything like a complete data model. I did not try running your attempted solution but hard coding of lengths and offsets within a case statement is the kind of solution that is bound to fail sooner rather than later. Received on Sat Jan 10 2009 - 08:02:01 CST