GROUP BY another table that have been grouped with two sub query

Question

I have table like this

Table1

ID      |    Val         |     Val2       |
606541  |3175031503131004|3175032612900004|
606542  |3175031503131004|3175032612900004|
677315  |3175031503131004|3175032612980004|
222222  |1111111111111111|8888888888888888|
231233  |1111111111111111|3175032612900004|
111111  |9999992222211111|1111111111111111|
57      |3173012102121018|3173015101870020|
59      |3173012102121018|3173021107460002|
2       |900             |7000            |
4       |900             |7001            |

I have two condition with column Val and Val2. Show the result if the Val:

1. Val column has at least two or more duplicate values AND
2. Val2 column has no duplicate value (unique)

For example :

Sample 1

 ID      |    Val         |     Val2       |
 606541  |3175031503131004|3175032612900004|
 606542  |3175031503131004|3175032612900004|  
 677315  |3175031503131004|3175032612980004|

 False, because  even the Val column 
 had two or more duplicate but the Val2 
 had dulicate value (ID 606541  and 606542)

Sample Expected 1 Result

 No records

Sample 2

 ID      |    Val         |     Val2       |
 222222  |1111111111111111|8888888888888888|
 231233  |1111111111111111|3175032612900004|   
 111111  |9999992222211111|1111111111111111|

 True, Because the condition is match, 
 Val column had duplicate value AND Val2 had unique values

Sample 2 Expected Result

 ID      |    Val         |     Val2       |
 222222  |1111111111111111|8888888888888888|
 231233  |1111111111111111|3175032612900004|

Sample 3

 ID      |    Val         |     Val2       |
 606541  |3175031503131004|3175032612900004|
 606542  |3175031503131004|3175032612900004|
 677315  |3175031503131004|3175032612980004|
 222222  |1111111111111111|8888888888888888|     
 231233  |1111111111111111|3175032612900004|
 111111  |9999992222211111|1111111111111111|

 Note : This is false condition, Because even the value for id 606541, 606542, and
 677315 in column Val had duplicate value at least 
 two or more but the value in column Val2 had no unique value (it could be true condition if id 606541, 
 606542, and 677315 had 3 different value on Val2).

 NOte 2 : for Id 222222 and 231233 that had duplicate value, this is still false, because the column 
 Val2 with ID 231233 had the same value with ID 606542 and 606541 (3175032612900004), so it didnt match 
 the second condition which only have no duplicate value

Sample 3 Expected Result

 No records

Now back to Table1 in the earlier, i tried to show result from the two condition with this query

SELECT
tb.* FROM table1 tb 
WHERE
    tb.Val2 IN (
    SELECT ta.Val2 
    FROM (
        SELECT
            t.* 
        FROM
            table1 t 
        WHERE
            t.Val IN ( 
            SELECT Val FROM table1 
            GROUP BY Val 
            HAVING count( Val ) > 1 ) 
        ) ta 
    GROUP BY
        ta.Val2 
    HAVING
    count( ta.Val2 ) = 1 
    )

The result

ID         Val                   Val2
677315  3175031503131004    3175032612980004
222222  1111111111111111    8888888888888888
57      3173012102121018    3173015101870020
59      3173012102121018    3173021107460002
2       900                  7000            
4       900                  7001 

While i expect the result was like this:

ID         Val                   Val2
57  3173012102121018    3173015101870020
59  3173012102121018    3173021107460002
2       900             7000            
4       900             7001            

Is there something wrong with my query ?

Here is my DB Fiddle.

Answer 1

Excuse for any mistakes as this would be my first answer in this forum. Could you also try with below, i agree to the answer with window function though.

SELECT t.*
FROM   table1 t 
WHERE  t.val IN (SELECT val 
                   FROM table1 
                 GROUP BY val 
                 HAVING COUNT(val) > 1 
                    AND COUNT(val) = COUNT(DISTINCT val2)
                 )
AND    t.val NOT IN (SELECT t.val
                     FROM   table1 t
                     WHERE  EXISTS (SELECT 1
                             FROM   table1 tai
                             WHERE  tai.id != t.id
                             AND    tai.val2 = t.val2));

/* first part of where clause makes sure we have distinct values in column val2 for repeated value in column val

second part of where clause with not in tells us there is no value shares across different ids with respect to value in column val2 */

--reverse order query ( not sure gives the expected result)

SELECT t.*
FROM   table2 t
WHERE  t.val IN (SELECT val FROM table2 GROUP BY val HAVING COUNT(val) = 1)
AND    t.val2 IN (SELECT t.val2
                  FROM   table2 ta
                  WHERE  EXISTS (SELECT 1
                          FROM   table2 tai
                          WHERE  tai.id != ta.id
                          AND    tai.val = ta.val));

Answer 2

You have to use Group By to find val & val2 with duplicate values and need to use Inner Join and Left Join in order to include/eliminate records as given conditions (oppose to IN, NOT IN etc. clauses that might cause performance issues in case you're dealing with large data).

Please find the query below:

select t1.*from table1 t1 left join
      (select val from table1
       where val2 in (select val2 from table1 group by val2 having count(id) > 1)
        ) t2
 on t1.val = t2.val
 inner join
     (select val from table1 group by val having count(id) >1) t3
     on t1.val = t3.val
 where t2.val is null

Query for Reverse Condition:

select t1.*from table1 t1 inner join
       (select val from table1 group by val having count(id) = 1)
         t2
 on t1.val = t2.val
 inner join
     (select val2 from table1 group by val2 having count(id) >1) t3
     on t1.val2 = t3.val2

Please find fiddle for both queries here.