CODEWITHSUNDEEP
mysqlsqljoin
richbai90
richbai90

Reputation: 5204

How to Join a Select and a Join select into a single query

I have 2 select statements in my query, an aggregate count, and a left join that combines 2 tables to give me some information I can't access otherwise. I know I can create a temporary table as both of those queries and then join them together on a common row, which in this case would be the user id. I just want to know if there's a way to do it in one query.

My queries are as follows:

SELECT usr, COUNT( quiz_id )
AS count
FROM edo_plugin_slickquiz_scores
WHERE usr_practice = 123456 
GROUP BY usr;

Gives me the following

USR    COUNT 
 6      1
 7      1

My second Query

SELECT a.user_id, a.meta_value, b.user_nicename
FROM edo_usermeta a LEFT JOIN edo_users b 
ON a.user_id = b.id
WHERE a.meta_key = 'user_practice_role';

Gives me the following

User_id  meta_value   user_nicename
-----------------------------------  
  6   |    5    |    richbai90
  5   |    1    |    hi5
  8   |    0    |    man
  7   |    1    |    testing123

That WHERE clause at the end is absolutely necesary due to how wordpress sets up their usermeta table. If you're familiar with wordpress you'll understand that this table looks like the following

umeta_id   user_id        meta_key      meta_value
--------------------------------------------------  
  1     |    1  |     first_name  |  bob    
  2     |    1  |     last_name   |  builder
  3     |    1  |     nickname    |  bob

So I only want values from this table where the meta_key is = to user_practice_role

Is the way I understand to do it the best way?

Upvotes: 0

Views: 61

Answers (2)

GarethD
GarethD

Reputation: 69749

As far as I can tell you don't need a temporary table at all, you can simply use one more join on your second query:

SELECT  a.user_id, a.meta_value, b.user_nicename, COUNT(sc.quiz_id) AS Quizes
FROM    edo_usermeta a 
        LEFT JOIN edo_users b 
            ON a.user_id = b.id
        LEFT JOIN edo_plugin_slickquiz_scores sc
            ON sc.usr = b.id
            AND sc.usr_practice = 123456 
WHERE   a.meta_key = 'user_practice_role'
GROUP BY a.user_id, a.meta_value, b.user_nicename;

Upvotes: 1

Kickstart
Kickstart

Reputation: 21513

Left join against a subselect?

SELECT a.user_id, a.meta_value, b.user_nicename, Sub1.QuizCount
FROM edo_usermeta a 
LEFT JOIN edo_users b 
ON a.user_id = b.id
LEFT JOIN 
(
    SELECT usr, COUNT( quiz_id ) AS QuizCount
    AS count
    FROM edo_plugin_slickquiz_scores
    WHERE usr_practice = 123456 
    GROUP BY usr;
) Sub1
ON a.user_id = Sub1.usr
WHERE a.meta_key = 'user_practice_role';

Upvotes: 0

Related Questions