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

Question

I'm trying to query two Wordpress tables, ie. posts and postmeta.

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

| ---- ** 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       |

score 3 · Accepted Answer · edited May 23 '17 at 12:15

3

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

edited May 23 '17 at 12:15

Community

1
1

answered Dec 05 '13 at 15:31

Filipe Silva

21,189
5
53
68

so, basically to get the players with numbers first on ascending order, and the the players without, i'll have to replace sql order to DESC, and then flip the result array… because now the null values are first – micadelli Dec 05 '13 at 15:39
You can add a validation to the `ORDER BY CASE WHEN b.meta_value IS NULL 2 ELSE 1 END,b.meta_value+(0) ASC, a.post_title ASC`. This will sort the ones that have b.meta_value before the ones that don't – Filipe Silva Dec 05 '13 at 15:44

score 0 · Answer 2 · answered Dec 05 '13 at 15:35

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

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

2 Answers2

Linked