Groupwise by groupwise select? I have two tables of data, such as:

CREATE TEMPORARY TABLE data_1 (user int, data int);
CREATE TEMPORARY TABLE data_2 (data int, file varchar(2));
INSERT INTO data_1 VALUES (1, 2), (1, 2), (2, 3), (2, 4), (3, 5);
INSERT INTO data_2 VALUES (1, 'a'), (3, 'a'), (4, 'a'), (5, 'b');

Table 1
user data
1    1
1    2
2    3
2    4
3    5
3    6

Table 2
data file
1    'a'
3    'a'
4    'a'
5    'b'
6    'c'


First I want to know, which users have more than one data point? Easy:

&gt; SELECT user FROM data_1 GROUP BY user HAVING COUNT(data) &gt; 1;
+------+
| user |
+------+
|    1 |
|    2 |
+------+


I also want to know which data-points are referenced to from more than one file.

&gt; SELECT data FROM data_2 GROUP BY file HAVING COUNT(file) &gt; 1;
+------+
| data |
+------+
|    1 |
+------+


This has the problem that GROUP BY file means we only get one data point per file, but that can be solved by a GROUP_CONCAT if necessary.

Now, but difficulty is that I want to combine both the conditions into a single query.

I want to know which users have more than one data point, for data points that together show up in the same file.

So the expected output from the sample data above would be 2.
User 2 would match because it has two datapoints, 3 and 4, and both of those have 'a' for 'file' value.

User 1 wouldn't match, because only one of data 1 and 2 is present in table 2. User 3 also would go away, because even though both data points 5 and 6 are in table two, they have different file values.

I could combine my two queries from above like this:

SELECT user FROM data_1 WHERE data IN (
  SELECT GROUP_CONCAT(data SEPARATOR ',') FROM data_2 GROUP BY file HAVING COUNT(file) &gt; 1
) GROUP BY user HAVING COUNT(data) &gt; 1;


But I think that fails on several ponts. First it appears like the group_concat turns the data ids into a string like '3, 4', so the IN () comparison is now not done on the correct numbers. but even if the IN did what I wanted, I expect this would apply the results from the inner query "globally" in the WHERE clause. I would need the condition applied groupwise to the outer query.
I think this will solve the problem. It shows users that have associated less distinct files than data.

Since the files associated for a user are less than the data, at least one file must be associated with more than one data (for that user):

SELECT 
    ud.user 
FROM 
    data_1 AS ud
      JOIN data_2 AS df
      ON ud.data = df.data
GROUP BY 
    ud.user
HAVING 
    COUNT(DISTINCT df.file) &lt; COUNT(*) ;


The query assumes you have UNIQUE constraints on data_1 (user, data) and on data_2 (data, file).