CODEWITHSUNDEEP
sqloracle-database
user3726933
user3726933

Reputation: 349

Oracle SQL - Eliminate values that are not equal on a join

Here's how my tables look: Both tables have a composite primary key on all 3 columns. And the SecondaryId will not repeat from these two tables. This is obviously not enforced by a DB constraint but by business rules on the front-end. 

Table1:
PrimaryID|SecondaryID|Email
1   9999    [email protected]
1   9999    [email protected]
2   8888    [email protected]
3   7777    [email protected]

Table2:
PrimaryID|SecondaryID|Email
1   1111    [email protected]
2   2222    [email protected]
3   3333    [email protected]

I want the secondaryid in both tables returned, joining on the same primaryid where the emails are different on all occurrences of the primary id.

For PrimaryID = 1: Table1 has two rows, table 2 has one row. But that one email from table2 matches with the one on table1 so I don't want that back. Here's my query but that doesn't eliminate the above duplicate which is what I need.

 SELECT DISTINCT T1.SECONDARYID, T2.SECONDARYID 
    FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.PRIMARYID = T2.PRIMARYID
    AND T1.EMAIL <> T2.EMAIL;

Returns

9999    1111
8888    2222

Upvotes: 0

Views: 1193

Answers (3)

Boneist
Boneist

Reputation: 23588

Here's another way without needing to query both tables twice:

WITH table1 AS (SELECT 1 PrimaryID, 9999 SecondaryID, '[email protected]' Email FROM dual UNION ALL
                SELECT 1 PrimaryID, 9999 SecondaryID, '[email protected]' Email FROM dual UNION ALL
                SELECT 2 PrimaryID, 8888 SecondaryID, '[email protected]' Email FROM dual UNION ALL
                SELECT 3 PrimaryID, 7777 SecondaryID, '[email protected]' Email FROM dual),
     table2 AS (SELECT 1 PrimaryID, 1111 SecondaryID, '[email protected]' Email FROM dual UNION ALL
                SELECT 2 PrimaryID, 2222 SecondaryID, '[email protected]' Email FROM dual UNION ALL
                SELECT 3 PrimaryID, 3333 SecondaryID, '[email protected]' Email FROM dual)
SELECT primaryid,
       t1_secondaryid,
       t2_secondaryid
FROM   (SELECT coalesce(t1.primaryid, t2.primaryid) primaryid,
               t1.secondaryid t1_secondaryid,
               t2.secondaryid t2_secondaryid,
               MAX(CASE WHEN t1.email = t2.email THEN 'Y' ELSE 'N' END) OVER (PARTITION BY coalesce(t1.primaryid, t2.primaryid)) same_email_present
        FROM   table1 t1
               FULL OUTER JOIN table2 t2 ON t1.primaryid = t2.primaryid AND t1.email = t2.email)
WHERE  same_email_present != 'Y';

 PRIMARYID T1_SECONDARYID T2_SECONDARYID
---------- -------------- --------------
         2                          2222
         2           8888

This uses a full outer join to pull all rows back regardless of whether they match or not. Then we can use an analytic function (I chose MAX() but you could easily switch to COUNT() or SUM() if you wanted to - you'd have to change the final filter to reflect that though) to output 'Y' for all rows if at least one of them has a matching email address.

Then it's just a matter of filtering out those rows which have a 'Y' present.

Upvotes: 1

user5683823
user5683823

Reputation:

You need to identify the id's for which there is at least one email in common in the two tables. One way to do that is to join on primaryid and email in a subquery. The solution below may not be the most efficient, but it will do the job.

select t1.primaryid,
       t1.secondaryid as secondaryid_in_t1,
       t2.secondaryid as secondaryid_in_t2
from   table1 t1 join table2 t2 
                 on t1.primaryid = t2.primaryid
where  t1.primaryid not in (
         select a.primaryid 
         from   table1 a join table2 b
                          on a.primaryid = b.primaryid and a.email = b.email
       )
;

Upvotes: 1

9000
9000

Reputation: 40894

So, you want to take all records from one table and find all such records from another table that the ID field is equal, but no record exists in the second table with an equal email.

I'd try something along these lines:

select one.*, another.*
from one, another
where
  one.id = another.id and
  not exists (
    select 1 
    from another as another_again
    where 
      another_again.id = one.id and
      another_again.email = one.email
  )

This might be not strictly the fastest query (hopefully Oracle can come up with a nice query plan). It should be easy to understand, though.

Upvotes: 1

Related Questions