Re: How to find out common records with Where clause

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Fri, 09 Jan 2009 17:56:22 -0600
Message-ID: <EqR9l.1805$FM6.481_at_flpi143.ffdc.sbc.com>

ddf wrote:
> 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

Looks like he really didn't know where to post to get someone to do his homework for him as it is also posted in the MySQL NG. Received on Fri Jan 09 2009 - 17:56:22 CST