查询具有相同ID的两个表的值

I currently have a database with two tables tc_services and tc_game_services, where these contain different information about the same service. They both are identified by the service_id field, where I need to pull the billing_id from one and game_id from the other.

我目前有一个包含两个表的数据库tc_services和tc_game_services，其中包含关于相同服务的不同信息。它们都由service_id字段标识，其中我需要从其中一个提取billing_id，从另一个提取game_id。

Now I found some examples in other questions, however when ran, they just provide me a list of these two values for every service, rather than the specific one I am trying to query from.

现在我在其他问题中找到了一些示例，但是当运行时，它们只是为每个服务提供这两个值的列表，而不是我要查询的特定值。

What do I need to correct in this query?

在这个查询中我需要纠正什么?

SELECT
  tcservices.billing_id,
  tcgameservices.game_id
FROM tc_services AS tcservices
INNER JOIN tc_game_services AS tcgameservices ON tcservices.service_id = '1812';

4 个解决方案

#1

This is very basic SQL. You didn't specify a joining condition on those tables. As you've mentioned, your common column is service_id. Connect both tables using this condition, and then in WHERE clause put the logic on retrieval of record for a specific service_id.

这是非常基本的SQL。您没有在这些表上指定连接条件。如前所述，公共列是service_id。使用此条件连接两个表，然后在WHERE子句中为特定的service_id将逻辑放在记录检索上。

SELECT
  tcservices.billing_id,
  tcgameservices.game_id
FROM tc_services AS tcservices
INNER JOIN tc_game_services AS tcgameservices ON tcservices.service_id = tcgameservices.service_id
WHERE tcservices.service_id = '1812';

---

Explaining the behaviour of your approach (the line below): you pulled every record from tc_services matching a criteria service_id = '1812' and then cross-joined it with every record from tc_gameservices. This is how you got your result.

解释方法的行为(如下一行):从匹配service_id = '1812'标准的tc_services中取出每个记录，然后与tc_gameservices中的每个记录交叉连接。这就是你的结果。

INNER JOIN tc_game_services AS tcgameservices ON tcservices.service_id = '1812'

---

I would suggest cutting the length of table aliases to save some typing and make the code look neater. This might particularly come in handy when dealing with larger and complicated queries.

我建议减少表别名的长度，以保存一些输入，使代码看起来更整洁。在处理较大和复杂的查询时，这可能特别有用。

As a side note, if your service_id is of Integer datatype then you don't need single quotes around the value.

顺便说一句，如果service_id是整数数据类型，则不需要在值周围使用单引号。

#2

SELECT
  tcservices.billing_id,
  tcgameservices.game_id
FROM tc_services AS tcservices
INNER JOIN tc_game_services AS tcgameservices ON tcservices.service_id = tcgameservices.service_id 
 where tcservices.service_id = '1812';

#3

Use ON to specify the relationhip between the two, so equality on service_id then filter the results with WHERE:

使用ON来指定两者之间的关系，所以service_id上的等式会将结果过滤为:

SELECT
  tcservices.billing_id,
  tcgameservices.game_id
FROM tc_services AS tcservices
INNER JOIN tc_game_services AS tcgameservices ON tcservices.service_id = tcgameservices.service_id
WHERE tcservices.service_id = 1812

#4

I think you should but the ID you're searching for in the WHERE clause and specify the column you want to join in your INNER JOIN.

我认为您应该在WHERE子句中搜索ID，并指定要在内部连接中加入的列。

SELECT tcservices.billing_id, tcgameservices.game_id
FROM tc_services AS tcservices
INNER JOIN tc_game_services AS tcgameservices ON tcservices.service_id = tcgameservices.service_id
WHERE tcservices.service_id = '1812';