Re: How to find out common records with Where clause

From: ddf <oratune_at_msn.com>
Date: Fri, 9 Jan 2009 07:57:48 -0800 (PST)
Message-ID: <46cc558c-c146-4b13-b5f1-bc73fa9633ba_at_p36g2000prp.googlegroups.com>

On Jan 9, 6:08 am, Sanjeev <sanjeev.atvan..._at_gmail.com> wrote:
> Dear Gurus,
>
> I have "VISITOR" table as follows.
>
> VisitNo  EmpName   Address
> --------------------------------------------
> 1           Sanjeev       Mumbai
> 2           Rajeev         New-Mumbai
> 3           Shailesh      Mumbai
> 4           Ramesh      New-Mumbai
>
> 5           Sanjeev       Pune-1
> 6           Rajeev         Pune-2
> 7           Shailesh      Pune-3
> 8           Shreyas      Pune-4
>
> 9           Sanjeev       Delhi
> 10         Rajeev         New-Delhi
> 11         Shailesh      Delhi
> 12         Shreyas      New-Delhi
>
> If I give location as Mumbai, Pune and Delhi i.e. location based
> pattern matching (LIKE operator) then
> query should retrieve record as Sanjeev,Rajeev and Shailesh
> i.e.
> all employees those are present in all given locations (Mumbai, Pune
> and Delhi).
> query shouldn't retrieve Ramesh, Shreyas because they are not present
> in all given location.
>
> I tried with "OR" operator but it did not work.
>
> Could any one help me in above.
>
> Thanking in advance
> Sanjeev

I'll give this a shot. Remember that this is based upon the current sample data provided by your post and may need to be modified if the data format changes:

SQL> --
SQL> -- Create table as defined
SQL> --
SQL> create table visitor(
  2          visitno number,
  3          empname varchar2(30),
  4          address varchar2(35)

  5 );

Table created.

SQL>
SQL> --
SQL> -- Insert sample data provided
SQL> --
SQL> insert all

  2 into visitor
  3 values(1 ,'Sanjeev','Mumbai')
  4 into visitor
  5 values(2 ,'Rajeev','New-Mumbai')
  6 into visitor
  7 values(3 ,'Shailesh','Mumbai')
  8 into visitor
  9 values(4 ,'Ramesh','New-Mumbai')
 10 into visitor
 11 values(5 ,'Sanjeev','Pune-1')
 12 into visitor
 13 values(6 ,'Rajeev','Pune-2')
 14 into visitor
 15 values(7 ,'Shailesh','Pune-3')
 16 into visitor
 17 values(8 ,'Shreyas','Pune-4')
 18 into visitor
 19 values(9 ,'Sanjeev','Delhi')
 20 into visitor
 21 values(10 ,'Rajeev','New-Delhi')
 22 into visitor
 23 values(11 ,'Shailesh','Delhi')
 24 into visitor
 25 values(12 ,'Shreyas','New-Delhi')
 26 select * From dual;

12 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Verify data load
SQL> --
SQL> select *

  2 from visitor;

   VISITNO EMPNAME                        ADDRESS
---------- ------------------------------
-----------------------------------
         1 Sanjeev                        Mumbai
         2 Rajeev                         New-Mumbai
         3 Shailesh                       Mumbai
         4 Ramesh                         New-Mumbai
         5 Sanjeev                        Pune-1
         6 Rajeev                         Pune-2
         7 Shailesh                       Pune-3
         8 Shreyas                        Pune-4
         9 Sanjeev                        Delhi
        10 Rajeev                         New-Delhi
        11 Shailesh                       Delhi

   VISITNO EMPNAME                        ADDRESS
---------- ------------------------------
-----------------------------------
        12 Shreyas                        New-Delhi

12 rows selected.

SQL>
SQL> --
SQL> -- Extract common part of
SQL> -- address and show who has
SQL> -- been where
SQL> --
SQL> -- Looking for those who
SQL> -- have visited all listed
SQL> -- locations
SQL> --
SQL> select empname, case when instr(address, '-') > 4 then substr
(address, 1, instr(address,'-')-1)
  2                       when instr(address, '-') < 5 then substr

(address, instr(address, '-')+1) end addr   3 from visitor
  4 order by empname;

EMPNAME                        ADDR
------------------------------ -----------------------------------
Rajeev                         Pune
Rajeev                         Delhi
Rajeev                         Mumbai
Ramesh                         Mumbai
Sanjeev                        Pune
Sanjeev                        Mumbai
Sanjeev                        Delhi
Shailesh                       Delhi
Shailesh                       Pune
Shailesh                       Mumbai
Shreyas                        Delhi

EMPNAME                        ADDR
------------------------------ -----------------------------------
Shreyas                        Pune

12 rows selected.

SQL>
SQL> --
SQL> -- Report the distinct locations
SQL> --
SQL> select distinct case when instr(address, '-') > 4 then substr
(address, 1, instr(address,'-')-1)
  2                       when instr(address, '-') < 5 then substr

(address, instr(address, '-')+1) end addr   3 from visitor;

ADDR

---

Delhi
Mumbai
Pune

3 rows selected.

SQL>
SQL> --
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);

EMPNAME

---

Sanjeev
Rajeev
Shailesh

3 rows selected.

SQL> David Fitzjarrell Received on Fri Jan 09 2009 - 09:57:48 CST