MySQL 从另一个表中选择带有 id 的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10562915/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
selecting rows with id from another table
提问by Jürgen Paul
Let's call this table terms_relation:
我们称这个表为terms_relation：
+---------+----------+-------------+------------+------------+--+
| term_id | taxonomy | description | created_at | updated_at | |
+---------+----------+-------------+------------+------------+--+
| 1 | categ | non | 3434343434 | 34343433 | |
| 2 | categ | non | 3434343434 | 3434343434 | |
| 3 | tag | non | 3434343434 | 3434343434 | |
| 4 | tag | non | 3434343434 | 3434343434 | |
+---------+----------+-------------+------------+------------+--+
And this is table terms:
这是表terms：
+----+-------------+-------------+
| id | name | slug |
+----+-------------+-------------+
| 1 | hello | hello |
| 2 | how are you | how-are-you |
| 3 | tutorial | tutorial |
| 4 | the end | the-end |
+----+-------------+-------------+
How Do I select all rows in table termsand table terms_relationwhere it's taxonomy in table terms_relationis categ? Will I need two queries for this or I could use a joinstatement?
我该如何选择表中的所有行terms和表terms_relation它的表分类terms_relation是categ？我是否需要两个查询，或者我可以使用一个join语句？
回答by gahcep
Try this (subquery):
试试这个（子查询）：
SELECT * FROM terms WHERE id IN
(SELECT term_id FROM terms_relation WHERE taxonomy = "categ")
Or you can try this (JOIN):
或者你可以试试这个（加入）：
SELECT t.* FROM terms AS t
INNER JOIN terms_relation AS tr
ON t.id = tr.term_id AND tr.taxonomy = "categ"
If you want to receive all fields from two tables:
如果要接收两个表中的所有字段：
SELECT t.id, t.name, t.slug, tr.description, tr.created_at, tr.updated_at
FROM terms AS t
INNER JOIN terms_relation AS tr
ON t.id = tr.term_id AND tr.taxonomy = "categ"
回答by Joseph Victor Zammit
You can use a subquery:
您可以使用子查询：
SELECT *
FROM terms
WHERE id IN (SELECT term_id FROM terms_relation WHERE taxonomy='categ');
and if you need to show all columns from both tables:
如果您需要显示两个表中的所有列：
SELECT t.*, tr.*
FROM terms t, terms_relation tr
WHERE t.id = tr.term_id
AND tr.taxonomy='categ'
回答by user1208484
SELECT terms.*
FROM terms JOIN terms_relation ON id=term_id
WHERE taxonomy='categ'
