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

本文是小编为大家收集整理的关于从另一个表中选择带有id的行的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

让我们称此表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 |  |
+---------+----------+-------------+------------+------------+--+

这是表terms:

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

如何选择表terms和表terms_relation中的所有行,表terms_relation中的分类法是categ?我将需要两个查询,还是可以使用join语句?

推荐答案

尝试此(子查询):

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

或者您可以尝试此(加入):

SELECT t.* FROM terms AS t 
   INNER JOIN terms_relation AS tr 
   ON t.id = tr.term_id AND tr.taxonomy = "categ"

如果您想从两个表接收所有字段:

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"

其他推荐答案

您可以使用子查询:

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

,如果您需要显示两个表的所有列:

SELECT t.*, tr.*
FROM terms t, terms_relation tr
WHERE t.id = tr.term_id
AND tr.taxonomy='categ'

其他推荐答案

SELECT terms.*
FROM terms JOIN terms_relation ON id=term_id
WHERE taxonomy='categ'

本文地址:https://www.itbaoku.cn/post/597767.html

问题描述

Let's call this 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 |  |
+---------+----------+-------------+------------+------------+--+

And this is table 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 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?

推荐答案

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"

其他推荐答案

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'

其他推荐答案

SELECT terms.*
FROM terms JOIN terms_relation ON id=term_id
WHERE taxonomy='categ'