How do I find common values in a table in Oracle?

Question

I have two tables - account and account_bal.

1. Each account can have multiple order id associated to it.
2. Each order have balance associated to it in account_bal table.

I am using below sql query and trying to get the desired output but it is happening somehow. How can I obtain order_id and customer id which have common balance ONLY. Could you please help me to get the correct output. Thanks.

select balance,order_id,account from account a ,account_bal b where a.order_id=b.order_id group by balance,order_id,account;

account_id	Order_id
923457586	3456
923457586	5678
923457586	2340
923457586	3456
923457587	5670
923457587	4354
923457587	7867
923457587	3400

Order_id	balance
3456	43
5678	23
2340	23
3456	11
5670	78
4354	90
7867	10
3400	10

Output:

account_id	Order_id	balance
923457586	5678	23
923457586	2340	23
923457587	7867	10
923457587	3400	10

Answer 1

Since you want multiple rows within each group, you want to use analytic functions (and not aggregation). You also need to remove duplicates from account:

SELECT account_id,
       order_id,
       balance
FROM   (
  SELECT a.account_id,
         a.order_id,
         b.balance,
         COUNT(*) OVER (PARTITION BY a.account_id, b.balance) AS cnt
  FROM   (SELECT DISTINCT * FROM account) a
         INNER JOIN account_bal b
         ON a.order_id=b.order_id
)
WHERE  cnt > 1;

Which, for the sample data:

CREATE TABLE account (account_id, Order_id) AS
SELECT 923457586, 3456 FROM DUAL UNION ALL
SELECT 923457586, 5678 FROM DUAL UNION ALL
SELECT 923457586, 2340 FROM DUAL UNION ALL
SELECT 923457586, 3456 FROM DUAL UNION ALL
SELECT 923457587, 5670 FROM DUAL UNION ALL
SELECT 923457587, 4354 FROM DUAL UNION ALL
SELECT 923457587, 7867 FROM DUAL UNION ALL
SELECT 923457587, 3400 FROM DUAL;

CREATE TABLE account_bal (Order_id, balance) AS
SELECT 3456, 43 FROM DUAL UNION ALL
SELECT 5678, 23 FROM DUAL UNION ALL
SELECT 2340, 23 FROM DUAL UNION ALL
SELECT 3456, 11 FROM DUAL UNION ALL
SELECT 5670, 78 FROM DUAL UNION ALL
SELECT 4354, 90 FROM DUAL UNION ALL
SELECT 7867, 10 FROM DUAL UNION ALL
SELECT 3400, 10 FROM DUAL;

Outputs:

ACCOUNT_ID	ORDER_ID	BALANCE
923457586	5678	23
923457586	2340	23
923457587	3400	10
923457587	7867	10

fiddle