Home » SQL & PL/SQL » SQL & PL/SQL » Analytical query (Oracle 11g)
Analytical query [message #661059] Mon, 06 March 2017 07:28 Go to next message
srinivas.k2005
Messages: 364
Registered: August 2006
Senior Member
Hi Team,

We need your help on below requirement on query formation.


Create table A(
ID number,
Address Varchar2(100))
;


Create table B(
sqlid number,
Vemail Varchar2(100))
;


Insert into A values (1083,'resale.x@att.com');

Insert into A values (1083,'tdoan@comt.com');

Insert into A values (1084,'Xyx@gh.com');

Insert into A values (1084,'abc@gh.com');

Insert into A values (1084,'fgt@pls.com');

Insert into A values (1001,'sthomas@TN.com');

Insert into A values (1001,'proxs@telep.com');

Insert into A values (1001,'cab@tow.net');

Insert into B values (1,'tdoan@comt.com');

Insert into B values (2,'sthomas@TN.com');

Insert into B values (3,'proxs@telep.com');



Below data looks like,

Table A Table B
ID Address sqlid Vemail

1083 resale.x@att.com 1 tdoan@comt.com
1083 tdoan@comt.com 2 sthomas@TN.com
1084 Xyx@gh.com 3 proxs@telep.com
1084 abc@gh.com
1084 fgt@pls.com
1001 sthomas@TN.com
1001 proxs@telep.com
1001 cab@tow.net

Here, we have two tables A and B which can be joined using A.Address=B.Vemail

The requirement is to get only those A.ID data from table A where no A.Address from table A present in B.Vemail from table B.


For example, query needs to pull ONLY 1084 ID data because none of its address matches with Vemail column in table B.

Results to get:

1084 Xyx@gh.com
1084 abc@gh.com
1084 fgt@pls.com


Query should NOT pull below data since its address is already matching with same ID.

1083 resale.x@att.com
1001 cab@tow.net


Please advise on the query.

Regards,
SRK


Re: Analytical query [message #661060 is a reply to message #661059] Mon, 06 March 2017 08:06 Go to previous messageGo to next message
cookiemonster
Messages: 12989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not exists will do what you need:
SQL> select * from a
  2  where not exists (select null
  3                    from a a2 join b on a2.address = b.vemail
  4                    where a2.id = a.id);
 
        ID ADDRESS
---------- --------------------------------------------------------------------------------
      1084 Xyx@gh.com
      1084 abc@gh.com
      1084 fgt@pls.com
 
SQL> 
Re: Analytical query [message #661062 is a reply to message #661060] Mon, 06 March 2017 08:55 Go to previous messageGo to next message
srinivas.k2005
Messages: 364
Registered: August 2006
Senior Member
Thanks a lot CookieMaster Smile
Re: Analytical query [message #661063 is a reply to message #661062] Mon, 06 March 2017 09:46 Go to previous message
cookiemonster
Messages: 12989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Alternatively, using analytics:
SQL> select id, address
  2  from (
  3  select a.id, a.address, count(b.vemail) over(partition by a.id) cnt
  4  from a left join b on a.address = b.vemail
  5  )
  6  where cnt = 0;
 
        ID ADDRESS
---------- --------------------------------------------------------------------------------
      1084 abc@gh.com
      1084 fgt@pls.com
      1084 Xyx@gh.com
 
SQL> 
Previous Topic: Delays between two dates excluding holidays/weekends
Next Topic: Date closest to event date
Goto Forum:
  


Current Time: Wed Jan 17 13:18:08 CST 2018

Total time taken to generate the page: 0.23109 seconds