Find values of user meta table when its grouped by relation filed in SQL-CodePudding

I want to filter records by exam_score and type Eg: find users where the exam is CAT and score between 50 to 100. please check these images. The exams are grouped by relation field value.

CodePudding user response：

Try this:

SELECT 
    um1.`user_id`, 
    um1.`value` AS exam_type, 
    um2.`value` AS score 
FROM user_meta AS um1
INNER JOIN (
    SELECT 
        `user_id`, `value`, `group` 
    FROM user_meta 
    WHERE `key` = 'score'
) AS um2 ON um1.`user_id` = um2.`user_id` 
         AND um1.`group` = um2.`group`
WHERE um1.`key` = 'exam_type' 
    AND um1.`value` = 'CAT' 
    AND um1.`group` = 'exam'
    AND CONVERT(um2.`value`, UNSIGNED INTEGER) BETWEEN 50 AND 100

With the filter exam took:

SELECT 
    um1.`user_id`, 
    um1.`value` AS exam_type, 
    um2.`value` AS score,
    um3.`value` AS took_exam
FROM user_meta AS um1
INNER JOIN (
    SELECT 
        `user_id`, `value`, `group` 
    FROM user_meta 
    WHERE `key` = 'score'
) AS um2 ON um1.`user_id` = um2.`user_id` 
         AND um1.`group` = um2.`group`
INNER JOIN (
    SELECT 
        `user_id`, `value`, `group` 
    FROM user_meta 
    WHERE `key` = 'took_exam'
) AS um3 ON um1.`user_id` = um3.`user_id` 
         AND um1.`group` = um3.`group`
WHERE um1.`key` = 'exam_type' 
    AND um1.`value` = 'CAT' 
    AND um1.`group` = 'exam'
    AND CONVERT(um2.`value`, UNSIGNED INTEGER) BETWEEN 50 AND 100

Another approach (Not preferable):

SELECT * FROM (
    SELECT 
        user_id,
        MAX(CASE WHEN `key` = 'exam_type' THEN `value` ELSE '' END) exam_type,
        MAX(CASE WHEN `key` = 'score' THEN CONVERT(`value` , UNSIGNED INTEGER) ELSE 0 END) score,
        MAX(CASE WHEN `key` = 'took_exam' THEN `value` ELSE '' END) took_exam
    FROM user_meta
    WHERE (`key` = 'exam_type' AND `value` = 'CAT') 
        OR (`key` = 'took_exam' AND `value` IN ('yes', 'no'))
        OR (`key` = 'score')
    GROUP BY user_id
) a WHERE score BETWEEN 50 AND 100