Finding duplicates in MYSQL table where data is in multiple tables (multiple conditions needed)

Question

My knowledge of MYSQL is rather basic, I would appreciate help with the following:

subrecords schema

id | record | element | title | name | type | value

The above table stores data submitted via a web form.

• Each row is one field (i.e. "phone number", "email", "subject" etc..) where "title" is the label of the field and "value" is the content of the submitted field
• the "record" identifies each unique submitted form, it is a foreign key of the "records" table (records.id) clarified below (the same form submitted several times generates multiple unique records)
• to know which form was submitted we need to look at another table

records schema

id | submitted | form | title | name

• id is unique to each submission (not unique to each form, the same form submitted multiple times generates multiple unique ids)
• form is a foreign key of another table (forms.id) that defines the structure of the form (all its fields etc..) and does not really simplify retrieving the data I need.
• records.name is a text string that identifies each unique form, this is needed to select the form we are looking for

I need to find all users (by email) that submitted the same form more than once, here are some further clarifications:

• all email addresses (subrecords.value where subrecords.title = 'email')
• that have more than one of the same form submitted (identified by records.name = "string-form-name").
• subrecords.record = record.id is needed simply to join the tables (each form submission is unique and generates a new record.id thus not helpful to identify neither the user nor the form).
• records.form is a foreign key = forms.id (it does not seem useful as it it complicates the query by needing to look into yet another table, it seems simpler to use records.name to identify the form)

So far thanks to another user I have this:

SELECT value     as email
      ,record
      ,COUNT(*)  as form_count
  FROM subrecords
 WHERE title = 'email'
   AND record IN (SELECT id
                    FROM records
                   WHERE name = 'form_name'
                 )
 GROUP BY value
         ,record
HAVING COUNT(*) > 1

It returns an empty value, but I am unable to narrow down how to improve this to make it work. Thank you

Answer 1

I think the problem is that you are grouping by record. A given record only has one form.

However, your query would be improved by switching from in to a join. Also, I'm not clear if you have a specific form name in mind. If so, then add and r.name = 'form_name' to the on clause:

SELECT r.form, s.value as email, COUNT(distinct s.record)  as form_count
FROM subrecords s join
     records r
     on s.record = r.id
WHERE s.title = 'email'
GROUP BY s.value, r.form
HAVING form_count > 1;