MySQL 左连接 WHERE table2.field = "X"

MySQL Left join WHERE table2.field = "X"

提问by Ivan Dokov

I have the following tables:

我有以下表格：

pages:

页：

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| page_id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| type       | varchar(20)  | NO   |     | NULL    |                |
| categories | varchar(255) | NO   |     | NULL    |                |
| title      | varchar(255) | NO   | MUL | NULL    |                |
| text       | longtext     | NO   | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

custom:

自定义：

+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| page_id | int(10) unsigned | NO   | PRI | NULL    |       |
| key     | varchar(255)     | NO   | PRI | NULL    |       |
| value   | longtext         | NO   |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+

I want to join the tables in a way where:
1) all the entries from the first table are returned LEFT JOIN custom ON pages.page_id = custom.page_id
2) pages.type IN ('type_a', 'type_b', 'type_c')
3) "key" from the second table has value "votes" custom.key = 'votes'

我想以一种方式加入这些表：
1) 返回第一个表中的所有条目LEFT JOIN custom ON pages.page_id = custom.page_id
2) pages.type IN ('type_a', 'type_b', 'type_c')
3) 第二个表中的“键”具有值“投票”custom.key = 'votes'

I made everything so far, but the third condition is the problem. If there isn't entry for key = 'votes'in table customthe query returns only these with entries. I want to return NULLif missing entries.

到目前为止我做了一切，但第三个条件是问题。如果key = 'votes'table custom 中没有条目，则查询仅返回这些条目。NULL如果缺少条目，我想返回。

I need key = 'votes', because I have other entries for this page_id where the key is not 'votes' and this duplicates the rows from pages

我需要key = 'votes'，因为我有这个 page_id 的其他条目，其中键不是“投票”，这会复制页面中的行

回答by dgw

Simply add your contraint custom.key='votes'to the LEFT JOIN

只需将您的限制添加custom.key='votes'到LEFT JOIN

SELECT * 
FROM pages LEFT JOIN custom 
     ON pages.page_id=custom.page_id AND custom.key='votes' 
WHERE pages.type IN('type_a','type_b','type_c') ;

回答by GregHNZ

I'd do it like this:

我会这样做：

SELECT * 
FROM pages 
LEFT JOIN
   ( SELECT * From custom where key='votes') cv
   on pages.page_id = cv.page_id
WHERE pages.type IN ('type_a', 'type_b', 'type_c');

回答by Euclides Mulémbwè

try changing your where condition to custom.key = 'votes' OR custom.key is null.

尝试将您的 where 条件更改为 custom.key = 'votes' OR custom.key 为空。