-1

I have 2 tables

table1:

id columnA
1 one
2 two
3 one

table2 :

table1_id columnB
1 row1
2 row2
3 row1

Is it possible to have a single query to fetch me the id from table1 where duplicate value in columnA also has duplicate value in columnB:

Like in this example, id 1 and 3 from table1 has duplicate values in columnA as well as columnB

Aritra Debsarma
  • 3
  • 2

2 Answers2

0
SELECT GROUP_CONCAT(table1.id)
FROM table1 
JOIN table2 ON table1.id = table2.table1_id
GROUP BY table1.columnA, table2.columnB
HAVING COUNT(*) > 1
Dharman
  • 30,962
  • 25
  • 85
  • 135
Akina
  • 39,301
  • 5
  • 14
  • 25
0

If it's ok to have Ids from same group as comma separated column then you can use below query (It will ensure that both table has same number of duplicate ids for a given columnA or columnB value):

Schema (MySQL v5.7)

create table table1 (id int, columnA varchar(20));
insert into table1 values(1,'one');                                              
insert into table1 values(2,'two');
insert into table1 values(3,'one');
create table table2 (table1_id int, columnB varchar(20));
insert into table2 values(1,'one');                                              
insert into table2 values(2,'two');
insert into table2 values(3,'one');

Query #1

select a.ids from
(SELECT GROUP_CONCAT(id) ids
FROM table1 
GROUP BY columnA
HAVING COUNT(*) > 1) a
inner join
(SELECT GROUP_CONCAT(table1_id) table1_ids
FROM table2
GROUP BY columnB
HAVING COUNT(*) > 1) b 
on a.ids=b.table1_ids;
ids
1,3

View on DB Fiddle

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
  • Thank you. This actually will help me with more complex queries. Already accepted Akina's answer but this helps a lot too. – Aritra Debsarma Mar 11 '21 at 06:00
  • No issues. That's more efficient. That's even better answer if number of duplicate items per table is not important to you. If table1 has three rows for one (1,3,4) and table two has two rows for row1 (1,3) and your desired output is (1,3) then Akira's answer is the right one. Best wishes – Kazi Mohammad Ali Nur Romel Mar 11 '21 at 06:09
  • @Akina That's what I have explained in my answer and comments. My answer will only generate answer if both table have same set of duplicate ids. – Kazi Mohammad Ali Nur Romel Mar 11 '21 at 06:20