Match duplicate value from the second column based on duplicate value from first colum

Question

I have table like this

CREATE TABLE table1 (
  `ID` VARCHAR(100),
  `Val` VARCHAR(100),
  `Val2` VARCHAR(100)
);

INSERT INTO table1
  (`ID`, `Val`, `Val2`)
VALUES
  ('1','4788','90'),
  ('2','4788','90'),
  ('10','4788','10'),
  ('20','111','10'),
  ('30','111','10'),
  ('57','89','89111'),
  ('59','89','89111'),
  ('60','89','10'),
  ('2','900','7000'),
  ('4','900','7001');

I have two condition for this table which is :

1. Val column must be duplicate value AND,
2. Val2 column must be duplicate

so my goal is return data if the two condition meet. If the column val had duplicate value and column val2 had duplicate value and each column on his own row.

my query looks like this

select t1.* from table1 t1
where exists (select 1 from table1 where id <> t1.id and val = t1.val)
and exists (
  select 1 from table1 
  where val = t1.val and val2 in (select val2 from table1 group by val2 having count(*) > 1)
)

the result was like this

ID  Val Val2
1   4788    90
2   4788    90
10  4788    10
20  111 10
30  111 10
57  89  89111
59  89  89111
60  89  10

As you can see the column did not match with each other

I expect the result data was like this

ID  Val Val2
1   4788    90
2   4788    90
20  111 10
30  111 10
57  89  89111
59  89  89111

here is my fiddle

Answer 1

You need a having and join. Here is the demo.

select 
   t.* 
from table1 t
join (
    select  
       val, val2 
    from table1 
    group by 
       val, val2 
    having count(*) > 1  
) t1 
on t.val = t1.val 
and t.val2 = t1.val2

output:

| ID  | Val  | Val2  |
| --- | ---- | ----- |
| 1   | 4788 | 90    |
| 2   | 4788 | 90    |
| 20  | 111  | 10    |
| 30  | 111  | 10    |
| 57  | 89   | 89111 |
| 59  | 89   | 89111 |