从表中选择行，其中另一个具有相同ID的表中的行在另一列中具有特定值 [英] Select rows from a table where row in another table with same id has a particular value in another column

问题描述

在MySQL中:

如果我们有两个表:

comments
key    |    value
=================
1      |    foo
2      |    bar
3      |    foobar
4      |    barfoo

和:

meta
comment_key    |    value
=========================
1              |    1
2              |    1
3              |    2
4              |    1

我想从comment表中获得注释，这些注释在meta表中具有对应的comment_key，并且具有特定的value(在meta表中的value列). /p>

例如，我想从comment表中选择meta表中所有value为1的行:

我期望这些结果:

key    |    value
=================
1      |    foo
2      |    bar
4      |    barfoo

如果我要从comment表中选择在meta表中所有value为2的行:

我希望得到这样的结果:

key    |    value
=================
3      |    foobar

我真的希望有人能提供帮助，谢谢大家！

我想需要加入吗?任何指针都是很好的，并且如果可能的话，可以做一个简短的解释，这样我就可以弄清楚哪里出了问题->这样我下次就知道了！

解决方案

我实际上不建议为此加入—或更确切地说，我建议您使用"半联接"，它是关系代数的概念不直接用SQL表示.半联接本质上是一种联接，您只想从一个表中检索记录，但前提是它们在不同的表中具有相应的记录.

在SQL表示法中，通过使用一个IN子句，其中带有子查询:

SELECT key, value
  FROM comments
 WHERE key IN
        ( SELECT comment_key
            FROM meta
           WHERE value = 1
        )
;

(MySQL实际上最终会在内部将其转换为半联接—本质上是一种简并的内部联接—但IN子句是在原始SQL中表达它的自然方式.)

In MySQL:

If we have two tables:

comments
key    |    value
=================
1      |    foo
2      |    bar
3      |    foobar
4      |    barfoo

and:

meta
comment_key    |    value
=========================
1              |    1
2              |    1
3              |    2
4              |    1

I want to get the comments from the comment table that have a corresponding comment_key in the meta table that have a specific value (the value column in the meta table).

For example, I'd like to select all the rows from the comment table that have a value of 1 in the meta table:

I'd expect these results:

key    |    value
=================
1      |    foo
2      |    bar
4      |    barfoo

And if I were to select all the rows from the comment table that have a value of 2 in the meta table:

I'd expect this result:

key    |    value
=================
3      |    foobar

I really hope someone can help, thank you all in advance!

I think I need to do a join? Any pointers would be great, and if at all possible, a short explanation so I can work out where I was going wrong -> so I'll know for next time!

解决方案

I actually wouldn't recommend a JOIN for this — or rather, I'd recommend a "semijoin", which is a relational-algebra concept not directly expressed in SQL. A semijoin is essentially a join where you want to retrieve records from only one table, but with the proviso that they have corresponding records in a different table.

In SQL notation, this concept is expressed indirectly, by using an IN clause, with a subquery:

SELECT key, value
  FROM comments
 WHERE key IN
        ( SELECT comment_key
            FROM meta
           WHERE value = 1
        )
;

(MySQL will actually end up translating that back into a semijoin internally — essentially a sort of degenerate inner-join — but the IN clause is the natural way to express it in raw SQL.)

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