从另一个表中选择具有ID的行 [英] selecting rows with id from another table

问题描述

我们称这个表为 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 |  |
+---------+----------+-------------+------------+------------+--+

是表术语：

+----+-------------+-------------+
| id |    name     |    slug     |
+----+-------------+-------------+
|  1 | hello       | hello       |
|  2 | how are you | how-are-you |
|  3 | tutorial    | tutorial    |
|  4 | the end     | the-end     |
+----+-------------+-------------+

如何选择表术语和表 terms_relation 在 terms_relation 中的分类法是 categ ？我需要两个查询，或者我可以使用 join 语句？

How Do I select all rows in table terms and table terms_relation where it's taxonomy in table terms_relation is categ? Will I need two queries for this or I could use a join statement?

推荐答案

尝试此子查询：

SELECT * FROM terms WHERE id IN 
   (SELECT term_id FROM terms_relation WHERE taxonomy = "categ")

或者，您可以尝试这个（JOIN）：

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"

这篇关于从另一个表中选择具有ID的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！