尽管另一个表可能没有值，但连接两个表的SQL查询 [英] SQL query to join two tables despite the other table may not have values

问题描述

我正在尝试查询两个Wordpress表，即. posts和postmeta.

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        |

我想获取post_type = player的所有帖子，并按postmeta.meta_value在postmeta.meta_key = number的位置进行排序，无论postmeta行是否存在.

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       |

推荐答案

使用LEFT JOIN代替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

您可以在有关JOINS的详细说明中看到，LEFT JOIN返回LEFT表中的所有结果，只要符合JOIN条件，就会添加RIGHT表中的信息.

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.

对于INNER JOIN，仅返回符合联接条件的结果

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

这篇关于尽管另一个表可能没有值，但连接两个表的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！