Need some help with a mySQL query since profiles can't prevent duplicates

[rcane]

From what I see, you can't prevent a person from entering a duplicate (existing) value in a custom profile field when registering.

So, I'm trying to get a query down that will show me any duplicates.

So far I can produce the actual field value that exists more than once, but I can't return the other row data to identify WHO had it....

I've tried all sorts of different methods to get duplicate rows, but none seems to work with the themes table.


I just need some guidance returning the `id_member` and `value` when  `variable' = 'cust_field' and the count of `value` > 1


Any suggestions?

[m4z]

Share your current code.

[rcane]

SELECT `id_member`, `value`
FROM proxythemes
WHERE `value` IN (
    SELECT `value`
    FROM proxythemes
    WHERE `variable` = 'cust_profile'
    GROUP BY`id_member`
    HAVING COUNT(`value`) > 1


Also something like this:

SELECT `id_member`, `value`
FROM proxythemes
WHERE `variable` = 'cust_profile'
   HAVING COUNT(`value`) > 1

Most of the examples I find out there are simple tables that don't behave like the themes table's variable/value column relationship.

[rcane]

Well, I think this one might be working




SELECT `id_member`, `variable`, proxythemes.value
FROM proxythemes
INNER JOIN(
SELECT `value`
FROM proxythemes
    WHERE `variable` = 'cust_profile'
    GROUP BY `value`
HAVING COUNT(`value`) >1
)temp ON proxythemes.value = temp.value;