选择一个值与另一个表中的另一个值匹配的行 [英] Select rows where a value matches another value in another table
问题描述
我有一个关于SQL查询的问题，我想让我希望有人可以帮助我。
I have a question in regards to an SQL query I am trying to make that I am hoping someone can help me out with.
所以我有三个表：活动，公司和公司组
So I have three tables: activity, company and companygroup
我只想在公司位于组1中时从活动中选择行。
但我也想用最终表中的公司名称。
I want to only select rows from the activity when the company is in group 1. But I also want to replace companyid with the company name in the final table.
活动表：
+----+---------+----------+-----------+
| id | subject | date | comapnyid |
+----+---------+----------+-----------+
| 1 | Do this | 10-10-13 | 20985 |
| 2 | Do that | 11-11-13 | 18657 |
| 3 | Dont do | 12-12-13 | 22039 |
+----+---------+----------+-----------+
公司表：
+----+-----------+-------------+
| id | companyid | companyname |
+----+-----------+-------------+
| 1 | 20985 | Compone |
| 2 | 18657 | Comptwo |
| 3 | 22039 | Compthree |
+----+-----------+-------------+
Companygroup表：
Companygroup table:
+----+-----------+---------+
| id | companyid | groupid |
+----+-----------+---------+
| 1 | 20985 | 1 |
| 2 | 20985 | 2 |
| 3 | 20985 | 3 |
| 4 | 18657 | 2 |
| 5 | 18657 | 3 |
| 6 | 22039 | 1 |
+----+-----------+---------+
我可以得到我的输出，以便在所有活动旁边给我一个带有公司名称的表，但是我不知道如何只选择与companyid匹配的组中的行我的companygroup表。我想我遇到麻烦了，因为每个companyid都有多个组条目，我不确定如何满足此要求。
I can get my output to give me a table with the company name next to all of the activities but I cannot figure out how to only select rows that the companyid matches up with group 1 in my companygroup table. I think I am having trouble because there are multiple group entries for each companyid, I am not sure how to cater for this.
推荐答案
尝试：
SELECT a.id, a.subject, a.date, c.companyname
FROM Activity a
INNER JOIN Company c ON a.companyid = c.companyid
INNER JOIN Companygroup cg ON c.companyid = cg.companyid
WHERE cg.groupid = 1
这篇关于选择一个值与另一个表中的另一个值匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
