WordPress User Table SQL Query – With Role, Meta, Multiple Table

These are sample WordPress SQL query for user table. Retrieve multiple sets of metadata for a user, for a specific set of users that meet certain criteria or When there is no relation between the two metadata entries, except that they are both applied to the same user.

SELECT u.ID, u.display_name, um1.*
FROM stp_users u 
LEFT JOIN stp_usermeta  um1 ON u.ID = um1.user_id
LEFT JOIN stp_usermeta  um2 ON u.ID = um2.user_id
WHERE um1.meta_value= '4' AND um1.meta_key = 'class_name'
AND um2.meta_key = 'keyA' AND um2.meta_value = 'valueA'
GROUP BY u.ID

//--------------------------------------
SELECT u.ID, u.display_name,s.class_id, s.student_id, b.batch_id, b.batch_name, b.batch_shortname, b.batch_session_start, b.batch_session_end, b.batch_shift
FROM stp_users u 
LEFT JOIN stp_usermeta  um1 ON u.ID = um1.user_id
LEFT JOIN stp_smgt_batchstudent  s ON u.ID = s.student_id
LEFT JOIN stp_smgt_batch b ON s.batch_id = b.batch_id
WHERE um1.meta_value= '4' AND um1.meta_key = 'class_name'
GROUP BY u.ID
//-------------------------------------------
SELECT u.ID, u.display_name,s.class_id, s.student_id, b.batch_id, b.batch_name, b.batch_shortname, b.batch_session_start, b.batch_session_end, b.batch_shift
FROM stp_users u 
LEFT JOIN stp_usermeta  um1 ON u.ID = um1.user_id
LEFT JOIN stp_smgt_batchstudent s ON u.ID = s.student_id
LEFT JOIN stp_smgt_batch b ON s.batch_id = b.batch_id
WHERE um1.meta_value= '4' AND um1.meta_key = 'class_name' AND b.batch_id = '297'
GROUP BY u.ID
//----------------------------------------
SELECT u.ID, u.display_name,s.class_id, s.student_id, b.batch_id, b.batch_name, b.batch_shortname, b.batch_session_start, b.batch_session_end, b.batch_shift
FROM stp_users u 
LEFT JOIN stp_usermeta  um1 ON u.ID = um1.user_id
LEFT JOIN stp_usermeta  um2 ON u.ID = um2.user_id
LEFT JOIN stp_smgt_batchstudent s ON u.ID = s.student_id
LEFT JOIN stp_smgt_batch b ON s.batch_id = b.batch_id
WHERE um1.meta_value= '4' AND um1.meta_key = 'class_name' AND b.batch_id = '297'
AND um2.meta_value LIKE '%student%' AND um2.meta_key LIKE '%capabilities%'
GROUP BY u.ID
//-------------------------------------
SELECT u.ID, u.display_name,um3.meta_value as first_name, um4.meta_value as last_name, s.class_id, s.student_id, b.batch_id, b.batch_name, b.batch_shortname, b.batch_session_start, b.batch_session_end, b.batch_shift
FROM stp_users u 
LEFT JOIN stp_usermeta um1 ON u.ID = um1.user_id
LEFT JOIN stp_usermeta um2 ON u.ID = um2.user_id
LEFT JOIN stp_usermeta um3 ON u.ID = um3.user_id AND um3.meta_key = 'first_name'
LEFT JOIN stp_usermeta um4 ON u.ID = um4.user_id AND um4.meta_key = 'last_name'
LEFT JOIN stp_smgt_batchstudent s ON u.ID = s.student_id
LEFT JOIN stp_smgt_batch b ON s.batch_id = b.batch_id
WHERE s.class_id = '4' AND um2.meta_value LIKE '%student%' AND um2.meta_key LIKE '%capabilities%'
GROUP BY u.ID ORDER BY um3.meta_value LIMIT 0, 10