SQL query to join two tables despite the other table may not have values

1 Views

I’m trying to query two WordPress tables, ie. posts and postmeta.

Basically the tables and rows I’m interested looks like this:

Read More
| ---- ** posts ** ---- |    | -------- ** postmeta ** ---------- |
| ID       | post_type  |    | post_id  | meta_key   | meta_value |
|-----------------------|    |------------------------------------|
| 1        | player     |    | 1        | number     | 10         |
| 2        | player     |    | 2        | number     | 20         |
| 3        | player     |    | 3        | othre_key  | aaa        |
| 4        | other_type |    | 4        | other_key  | xxx        |
| 4        | other_type |    | 5        | other_key  | yyy        |
| 4        | other_type |    | 6        | other_key  | zzz        |

I want to get all posts where post_type = player and order them by postmeta.meta_value where the postmeta.meta_key = number whether or not the postmeta row exists.

so far I’ve got this: 

SELECT a.ID, a.post_title, b.meta_value, b.meta_key
FROM $wpdb->posts AS a
INNER JOIN $wpdb->postmeta AS b
    ON a.ID = b.post_id
    AND b.meta_key = %s
WHERE a.post_status = %s
    AND a.post_type = %s
GROUP BY a.ID
ORDER BY b.meta_value+(0) ASC, a.post_title ASC

…which returns the players that has a number postmeta associated with them.

How do I append the players that doesn’t have that postmeta to the result within same query so that the desired result would look something like this? 

| --- ** result ** ---- |
| ID       | meta_value |
|-----------------------|
| 1        | 10         |
| 2        | 20         |
| 3        | null       |
Post Views: 1

Related posts

Leave a Reply

2 comments

  1. Use LEFT JOIN instead of INNER JOIN:

    SELECT a.ID, a.post_title, b.meta_value, b.meta_key
FROM $wpdb->posts AS a
LEFT JOIN $wpdb->postmeta AS b
    ON a.ID = b.post_id
    AND b.meta_key = %s
WHERE a.post_status = %s
    AND a.post_type = %s
GROUP BY a.ID
ORDER BY b.meta_value+(0) ASC, a.post_title ASC

    As you can see in this great explanation of JOINS, LEFT JOIN returns all the results from the LEFT table and adds the information that is in the RIGHT table, whenever the JOIN condition is matched.

    As for INNER JOIN, only returns results that match the join condition

  2. As Filipe said, you want to use LEFT JOIN.

    The left join is for the most part the same as a inner join. It puts together all matching records, according to the on specification.

    The difference is that all records from the LEFT Table, (the table you wrote before the join operator), will also be returned in the query results.

    All values from the right table fields will be set to NULL.

    Further more, to get only rows from the left table that doesn’t match the right table, you could add a where b.post_id IS NULL.

    SELECT a.ID, a.post_title, b.meta_value, b.meta_key
FROM $wpdb->posts AS a
LEFT JOIN $wpdb->postmeta AS b
    ON a.ID = b.post_id
    AND b.meta_key = %s
WHERE a.post_status = %s
    AND a.post_type = %s
GROUP BY a.ID
ORDER BY b.meta_value+(0) ASC, a.post_title ASC