Can not get my SQL Select statement to work with CASE

Question

SQL Noob here. Pulling some data from a custom table in a WP install and I want to get the user information from both user_meta and users table.

The resulting array should look like this.

array(
 user_id => 1234,
 user_email => [email protected]
 first_name => foo
 last_name => bar
)

I just cant get my CASE statement right.

$query = $wpdb->prepare(
    "SELECT s.user_id, u.user_email         
    FROM wp_user_stages s 
    INNER JOIN wp_users u ON s.user_id=u.ID 
    INNER JOIN wp_usermeta m ON s.user_id=m.user_id
    CASE 
        WHEN m.met_key = 'first_name' THEN m.meta_value AS first_name
        WHEN m.met_key = 'last_name' THEN m.meta_value AS last_name
    END
    WHERE 
        s.quest_comp = 0 AND
        s.registered = %s           
    ",
    '2024-05-11'    
    );
$users = $wpdb->get_results($query, ARRAY_A);

I've looked at various manuals and tried the case statement in different formats and different places. I've used it before but so long ago I just cant remember.

Answer 1

You need to aggregate by user and then use pivoting logic to extract the key values:

SELECT
    s.user_id,
    u.user_email,
    MAX(CASE WHEN m.met_key = 'first_name' THEN m.meta_value END) AS first_name,
    MAX(CASE WHEN m.met_key = 'last_name' THEN m.meta_value END) AS last_name       
FROM wp_user_stages s 
INNER JOIN wp_users u ON s.user_id = u.ID 
INNER JOIN wp_usermeta m ON s.user_id = m.user_id
WHERE 
    s.quest_comp = 0 AND
    s.registered = %s
GROUP BY
    s.user_id,
    u.user_email