Moodle SQL report

Question

I am trying to make an SQL report. In this reports there would be "Duplicated users", "Special users", "Test users" and "Normal users". There is a checkbox for users (only the administrator sees this checkbox) and if the admin ticks this chechkbox the user will be a "Duplicated user", special users will be those who have xy.com in their email addresses and test users will be those who have the word "test" in their firstnames and all the other users will be normal users. My problem is if a user is a duplicated user then they appear twice in this report, I think I know why but I don't know how to solve it, they appear once as normal users and once as duplicated user but I only want them to appear as duplicated users, here is my code:

SELECT distinct(u.email), u.username, u.lastname, if(DATE_FORMAT(FROM_UNIXTIME(u.firstaccess),'%Y-%m-%d') = '1970-01-01', 'Never logged in', DATE_FORMAT(FROM_UNIXTIME(u.firstaccess),'%Y-%m-%d')) AS 'First log in', u.firstname,

CASE 

WHEN uif.shortname = 'duplicated' and uid.data = 1 THEN 'Duplicated user'

WHEN u.firstname like '%test%' THEN 'Test user'

WHEN u.email like '%@xy.com%' THEN 'Special user'

ELSE

'Normal user'

END AS Usertype

FROM prefix_user AS u

JOIN prefix_user_info_data AS uid ON uid.userid = u.id

JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id

where u.deleted = 0 and u.username <> 'guest'

score 0 · Accepted Answer · answered Jun 04 '22 at 11:48

You might be getting duplicates because there is more than one custom field

Try changing the join to this

LEFT JOIN (
    SELECT uid.userid, uid.data
    FROM prefix_user_info_data AS uid
    JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id AND uif.shortname = 'duplicated'
) uid ON uid.userid = u.id

and the case to this

WHEN uid.data = '1' THEN 'Duplicated user'

Moodle SQL report

1 Answers1