0

How can I select only those rows which appear in query 1 and not query 2? The goal is to find those reports which have multiple occurrences of the same item.

SELECT finance_report_id, finance_report_item_id FROM finance_report_values;

SELECT DISTINCT finance_report_id, finance_report_item_id FROM finance_report_values;

I was hoping something like the following would work. (This fails because id isn't selected in the second query.)

SELECT t1.finance_report_id FROM finance_report_values t1
    LEFT JOIN (SELECT DISTINCT finance_report_id, finance_report_item_id FROM finance_report_values) t2
    ON  t1.id = t2.id
    WHERE t1.id IS NULL;

I suspect I'm not the first asking this, but I wasn't able to find the same problem posted.

Simon
  • 123
  • 3
  • 9
  • Add `id` to the second query. And it should be `WHERE t2.id IS NULL` – Barmar Sep 08 '22 at 22:45
  • 1
    `select finance_report_id, finance_report_item_id FROM finance_report_values GROUP BY finance_report_id, finance_report_item_id HAVING COUNT(*) > 1` – Barmar Sep 08 '22 at 22:46
  • If the goal is to find duplicates, why do you want the rows that *don't* appear in query 2? – Barmar Sep 08 '22 at 22:47
  • Thank you for your comments! My question was correctly marked as a duplicate :) – Simon Sep 09 '22 at 20:17

0 Answers0