MySQL Left Join

1 Views

I have 2 tables (WordPress Database), one which stores posts and the other post meta. For this example, the posts table is storing information about venues (i.e. bars or clubs). The meta table is storing a rating given to these bars by users/visitors. Ratings are stored as either 20, 40, 60, 80 or 100 under the key ‘_rating’. I have the following query. 

SELECT posts.*, AVG(meta.meta_value) as average 
FROM wp_posts as posts 
LEFT JOIN wp_postmeta as meta 
     ON posts.ID = meta.post_ID 

WHERE meta.meta_key = '_rating' 
     AND posts.post_status = 'publish' 
     AND posts.post_type = 'npc_venue' 

GROUP BY meta.post_id 
ORDER BY post_title asc

This works great except for one issue. If a venue has not yet received a rating, then there is no ‘_rating’ entry in the wp_postmeta table, and thus it is not pulled in the query.

Read More

Question is: How can I pull all venues from the database, with their average rating, even when they have not yet received a rating. Thanks in advance!

EDIT: If a venue has not been rated yet, then there is no _rating entry in the wp_postmeta table, which brings up the question, what does NULL mean exactly? NULL is typically used for an unknown value. However, when the entry is missing completely from the table, is it still considered NULL? Or does it just become an empty rowset?

Post Views: 1

Related posts

Leave a Reply

4 comments

  1. Put meta.meta_key = '_rating' in the join condition, not in the WHERE clause, i.e. (untested):

    SELECT posts.*, AVG(meta.meta_value) as average 
FROM wp_posts as posts 
LEFT JOIN wp_postmeta as meta 
     ON posts.ID = meta.post_ID
    AND meta.meta_key = '_rating'

WHERE posts.post_status = 'publish' 
  AND posts.post_type = 'npc_venue' 

GROUP BY posts.ID 
ORDER BY post_title asc

    Also note the change in the GROUP BY clause; if there are no matching metadata records, meta.post_ID will be NULL.

  2. try something like …

        SELECT posts.*,
    (SELECT AVG(meta.meta_value)
        FROM wp_postmeta as meta
        WHERE posts.ID = meta.post_ID
            AND meta.meta_key = '_rating' 
            AND posts.post_status = 'publish' 
            AND posts.post_type = 'npc_venue) as average
    ORDER BY post_title ASC

    … not sure if this will work as-is, but the idea is there… make “average” a calculated field rather than a join.