MySQL using results of a query that has distinct counts in another query

Question

I hope someone can help with this!

I've used the following query to count the distinct values in column2 and column3 and filter the results:

SELECT 
column1, 
COUNT(DISTINCT(column2)), 
COUNT(DISTINCT(column3))

FROM table1
GROUP BY column1
HAVING COUNT(DISTINCT(column2)) > 1
OR COUNT(DISTINCT(column3)) > 1

The output looks something like this:

| column1  | column2  | column3 |
| -------- | -------- |         |
| abcd     |   1      |     2   |
| efgh     |   2      |     2   |
| ijkl     |   2      |     1   |

I would now like to see what these distinct values of column2 and column3 associated with each value of column1 are. So, abcd will have one distinct value from column1 but two distinct values from column2- so I'm expecting the output to have two rows for abcd, with the value in column1 repeating, and two different values in column2

Does anyone know how to go about this?

Answer 1

Let a subquery return the column1 values having more than one distinct column2 or column3 values.

select distinct column1, column2, column3
from table1
where column1 in (select column1
                  from table1
                  group by column1
                  having count(distinct column2) > 1
                      or count(distinct column3) > 1)