Output should be like this

How to compare two rows having same key1 but different key2 where val is same for key2's.

I tried having and group by query but as this outcome render from views and 3 different tables, which is hard to understand for me. Any help will be appreciated

Added : where key1 is same for key2 having same val need to exclude but when key1 is same for key2 having different val need to show as outcome

CodePudding user response：

To me, it looks like

SQL> WITH
  2     test (key1, key2, val)
  3     AS
  4        -- sample data
  5        (SELECT '23', 215, 4489 FROM DUAL
  6         UNION ALL
  7         SELECT '23', 216, 4489 FROM DUAL
  8         UNION ALL
  9         SELECT '86', 326, 5245 FROM DUAL
 10         UNION ALL
 11         SELECT '86', 325, 4489 FROM DUAL
 12         UNION ALL
 13         SELECT '86', 323, 4489 FROM DUAL
 14         UNION ALL
 15         SELECT '04', 369, 1200 FROM DUAL
 16         UNION ALL
 17         SELECT '04', 370, 1673 FROM DUAL
 18         UNION ALL
 19         SELECT '04', 368, 4489 FROM DUAL
 20         UNION ALL
 21         SELECT '10', 402, 1673 FROM DUAL
 22         UNION ALL
 23         SELECT '10', 400, 5971 FROM DUAL
 24         UNION ALL
 25         SELECT '10', 404, 1200 FROM DUAL
 26         UNION ALL
 27         SELECT '10', 401, 9189 FROM DUAL),

 28     temp
 29     AS
 30        -- count > 1 means that there are values that match
 31        (  SELECT key1, val, COUNT (*) cnt
 32             FROM test
 33         GROUP BY key1, val)
 34    SELECT a.key1, a.key2, a.val
 35      FROM test a
 36     WHERE (a.key1, a.val) IN (SELECT b.key1, b.val
 37                                 FROM temp b
 38                                WHERE b.cnt = 1)     --> no match
 39  ORDER BY a.key1, a.key2;

KEY1           KEY2        VAL
-------- ---------- ----------
04              368       4489
04              369       1200
04              370       1673
10              400       5971
10              401       9189
10              402       1673
10              404       1200
86              326       5245

8 rows selected.

SQL>

If sample data come from a query you already wrote, then you'd use it as a CTE:

with your_query as
  (select ... --> put your query in here
   from ...
   where ...
  ),
-- code I wrote goes here, starting from line #28
temp as
...

After you posted your query, it would be something like this; note that I don't know which columns you want to compare because there are no longer KEY1, KEY2 and VAL columns; you'll have to fix it yourself.

WITH
   your_query
   AS
      (  SELECT COUNT (*) cnt,
                t2.ORIGINATOR_ID,
                t3.name,
                t4.MULTI_TRANCHE_FLAG,
                v1.SOURCE_OF_DEAL,
                ta1.PRICING_DATE
           FROM vw_origination_deal_advice ta1
                LEFT JOIN vw_orig_deal_advice_summary v1
                   ON v1.deal_id = ta1.orig_deal_advice_id
                LEFT JOIN tbl_issue_tranche t4 ON t4.id = ta1.ISSUE_TRANCHE_ID
                LEFT JOIN tbl_issue_originator t2
                   ON ta1.issue_tranche_id = t2.ISSUE_TRANCHE_ID
                LEFT JOIN tbl_staff t3 ON t2.originator_id = t3.staff_id
          WHERE     ta1.ISSUE_TRANCHE_ID IN
                       (  SELECT ta2.ISSUE_TRANCHE_ID
                            FROM vw_origination_deal_advice ta2
                        GROUP BY ta2.ISSUE_TRANCHE_ID)
                AND ta1.PRICING_DATE LIKE '%-21%'
                AND t4.MULTI_TRANCHE_FLAG = 1
       GROUP BY ta1.orig_deal_advice_id,
                ta1.ISSUE_TRANCHE_ID,
                t4.ACTIVE,
                t2.ORIGINATOR_ID,
                t3.name,
                t4.MULTI_TRANCHE_FLAG,
                v1.SOURCE_OF_DEAL,
                ta1.PRICING_DATE),
   temp
   AS
      -- count > 1 means that there are values that match.
      -- As KEY1, KEY2, VAL don't exist in your query, you'll have to use appropriate
      -- names from in the rest of the query
      (  SELECT key1, val, COUNT (*) cnt               
           FROM test
       GROUP BY key1, val)
  SELECT a.key1, a.key2, a.val
    FROM test a
   WHERE (a.key1, a.val) IN (SELECT b.key1, b.val
                               FROM temp b
                              WHERE b.cnt = 1)                    --> no match
ORDER BY a.key1, a.key2;