通过在另一个表中查找插入记录(Access 2007)

Question

我有一个datagridview行，包含三个字符串值。应该在Products表中查找这些值，以查找相应的产品ID。然后这些将被插入到关系表中。我正在寻找实现这一点的最佳查询。

Here is my Products Table
+------------+--------------+
| Product_ID | Product_Name |
+------------+--------------+
|          1 | Foo          |
|          2 | Bar          |
|          3 | Baz          |
|          4 | NewProduct   |
+------------+--------------+

以及我试图插入的关系表

+------------+----------------+-----------------+
| Product_Id | RelatedProd_Id | RelatedProd_Id2 |
+------------+----------------+-----------------+
|          1 | 2              | null            |
|          2 | 3              | 1               |
|          3 | null           | null            |
+------------+----------------+-----------------+

下面的不是一个表，它是一个示例数据网格视图行。。

+------------+--------------+---------------+
|  ProdName  | RelProd_Name | RelProd_Name2 |
+------------+--------------+---------------+
| NewProduct | Foo          | Bar           |
+------------+--------------+---------------+

我正在尝试从此行中查找ID，并将其插入Relations表中。

我尝试了一个愚蠢的查询。。但是我不知道做这件事的正确方法，比如

INSERT INTO PROD_RELATIONS (Product_id,RelatedProd_Id,RelatedProd_Id2)
VALUES
(SELECT Product_Id FROM Products WHERE Product_Name = 'NewProduct'),
(SELECT Product_Id FROM Products WHERE Product_Name = 'Foo'),
(SELECT Product_Id FROM Products WHERE Product_Name = 'Bar')

有人能指导我吗？

Answer 1

对于当前的表结构，这样的查询可以工作：

INSERT INTO Prod_Relations (Product_ID, RelatedProd_ID1, RelatedProd_ID2)
SELECT  t1.Product_ID, t2.PRoduct_ID, t3.Product_ID
FROM    Products t1, Products t2, Products t3
WHERE   T1.Product_Name = 'NewProduct'
AND     t2.Product_Name = 'Foo'
AND     t3.Product_Name = 'bar'

然而，我建议将关系表更改为更简单的布局，每个产品有多行：

Product_ID  |   RelatedProd_ID
------------+-----------------
    4       |       1
    4       |       2   

这意味着，如果一个产品有两个以上的关系，就不必添加更多的列。在这种情况下，您的插入语句将是：

INSERT INTO Prod_Relations (Product_ID, RelatedProd_ID)
SELECT  t1.Product_ID, t2.PRoduct_ID
FROM    Products t1, Products t2
WHERE   T1.Product_Name = 'NewProduct'
AND     t2.Product_Name IN ('Foo', 'Bar')

如果需要，您可以随时查询产品关系表，将其恢复为2列格式

SELECT  t1.Product_ID, 
        MIN(t1.RelatedProd_ID) AS [RelatedProd_ID1],
        MIN(t2.RelatedProd_ID) AS [RelatedProd_ID2]
FROM    Prod_Relations t1
        LEFT JOIN Prod_Relations t2
            ON t2.Product_ID = t1.Product_ID
            AND t2.RelatedProd_ID > t1.RelatedProd_ID
GROUP BY t1.Product_ID

Answer 2

您可以使用Access的DLookup函数来检索INSERT INTO PROD_RELATIONS的值。

INSERT INTO PROD_RELATIONS (
    Product_id,
    RelatedProd_Id,
    RelatedProd_Id2
    )
VALUES (
    DLookup("Product_Id", "Products", "Product_Name = 'NewProduct'"),
    DLookup("Product_Id", "Products", "Product_Name = 'Foo'"),
    DLookup("Product_Id", "Products", "Product_Name = 'Bar'")
    );

将其与您的示例查询进行比较，并注意到单个SELECT片段如何容易地转换为DLookup()表达式。DLookup与返回一个值的SELECT查询非常相似。

如果你按照Gareth的建议重新设计你的表，你可以调整这种DLookup方法来适应这种结构。