连接来自两个没有唯一键的表的数据

I have two tables in one database (access file).

我在一个数据库中有两个表(访问文件)。

Table 1:

表1:

| Product Id |  Year     | Sales |
----------------------------------
| 1144       |  2013     | 100   |
| 20131120   |  2013     | 200   |
| 1144       |  2012     | 333   |

Table 2:

表2:

| Product Id     |  Category     |
----------------------------------
| 1144           | Car           |
| 20131120       | Motorbike     |

The first table (table 1) is the table when user can add new data whenever wants, and the second table is updated once in a while, when new product id appears. I would like to join this table, some kind of relation on the base of Product Id, but I'm not able to use relation due to the lack of unique, primary key, am I right? When I execute SQL query ( SELECT * FROM Table 1) I would like to get the result like this one:

第一个表(表1)是用户可以随时添加新数据的表，第二个表在出现新产品id时偶尔更新一次。我想加入这个表格，某种基于产品Id的关系，但是由于缺少唯一的主键，我不能使用关系，对吗?执行SQL查询时(从表1中选择*)，我希望得到如下结果:

| Product Id |  Year     | Sales | Category   |
-----------------------------------------------
| 1144       |  2013     | 100   | Car        |
| 20131120   |  2013     | 200   | Motrobike  |
| 1144       |  2012     | 333   | Car        |

I know I can join this two tables, but I would like not to do it every time when user add new data to table 1. Do you know any solution how could I do that?

我知道我可以加入这两个表，但是我不想每次用户向表1添加新数据时都这么做。你知道怎么解决吗?

1 个解决方案

#1

I'm not able to use relation due to the lack of unique, primery key, am I right?

我不能使用关系因为缺少唯一的，昂贵的钥匙，我是对的吗?

No, this is not right, you can normally JOIN the two tables. It’s also legal to JOIN two tables or result sets on any columns that have what so called eligible data types.

不，这是不对的，您通常可以加入这两个表。对于任何具有所谓的合格数据类型的列，连接两个表或结果集也是合法的。

SELECT
  t1.ProductId, t1.year, t1.sales, t2.category
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.productId = t2.productId;

but I would like not to do it every time when user add new data to table 1

但是我不希望每次用户向表1添加新数据时都这样做

You don't need a ProductId in the second table, you need a CategoryId instead, and add a new column CategoryId to the first table too and declare it as a foreign key. Your tables should look like so:

在第二个表中不需要ProductId，而是需要一个CategoryId，并在第一个表中添加一个新的列CategoryId并将它声明为外键。您的表应该是这样的:

Table1 (Products):

表1(产品):

ProductId,
ProductId,
CategoryId,
被标记,
Year,
一年,
Sales.
销售。

Table2 (Categories):

表二(类别):

CategoryId,
被标记,
CategoryName.
CategoryName。

Then the category is added once to the categories table table2 and the product is added once to the products table table1. And for each product enter a CategoryId for it. This what so called normalization

然后将类别添加一次到categories表2，将产品添加一次到product表1。并为每个产品输入一个类别id。这就是所谓的标准化

You should also define ProductId in table1 and CategoryID in table2 as primary keys, and CategoryID in the first table as foreign key.

还应该将表1中的ProductId和表2中的CategoryID定义为主键，将第一个表中的CategoryID定义为外键。

#1