尽管另一个表可能没有值,但连接两个表的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屋!