Analytical query [message #661059] |
Mon, 06 March 2017 07:28 |
srinivas.k2005
Messages: 404 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 |
cookiemonster
Messages: 13943 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 #661063 is a reply to message #661062] |
Mon, 06 March 2017 09:46 |
cookiemonster
Messages: 13943 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>
|
|
|