MySQL左连接WHERE table2.field ="X" [英] MySQL Left join WHERE table2.field = "X"
问题描述
我有下表:
页面:
+------------+--------------+------+-----+---------+----------------+
| 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 | |
+------------+--------------+------+-----+---------+----------------+
自定义:
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| page_id | int(10) unsigned | NO | PRI | NULL | |
| key | varchar(255) | NO | PRI | NULL | |
| value | longtext | NO | | NULL | |
+---------+------------------+------+-----+---------+-------+
我想通过以下方式加入表:
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 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'
到目前为止,我已完成了所有工作,但第三个条件是问题.如果表自定义中没有key = 'votes'
的条目,则查询仅返回包含条目的条目.如果缺少条目,我想返回NULL
.
I made everything so far, but the third condition is the problem. If there isn't entry for key = 'votes'
in table custom the query returns only these with entries. I want to return NULL
if missing entries.
我需要key = 'votes'
,因为我还有此page_id的其他条目,键不是投票",并且这会复制页面
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
推荐答案
只需将约束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') ;
这篇关于MySQL左连接WHERE table2.field ="X"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!