如何基于一列将列添加到另一个表？

I have my shop database and I want to join two tables together.

我有我的商店数据库,我想一起加入两张桌子。

id_order | reference | id_shop_group | id_shop | id_carrier | id_lang | id_customer | id_cart

This the header row of my orders table and below is the header of customers table.

这个我的订单表和下面的标题行是customers表的标题。

id_customer | id_shop_group | id_shop | id_gender | firstname | lastname 

What I want to do is to join them based on id_customer column. More specifically I want to add all columns of customers except the ones that are already there to orders table based onid_customer. After joining the tables should look like this:

我想要做的是基于id_customer列加入它们。更具体地说,我想添加所有客户列,除了已经存在的客户列基于onid_customer的订单表。加入表后应如下所示:

id_order|reference|id_shop_group|id_shop|id_carrier|id_lang|id_customer|id_cart|id_gender|firstname|lastname 

When searching for a solution I found INNER JOIN keyword, but I'm not sure how to use it the way I want.

在搜索解决方案时,我找到了INNER JOIN关键字,但我不确定如何以我想要的方式使用它。

2 个解决方案

#1

We don't "Add columns to a table". We, instead, submit SQL to the database that returns the result set that we want. In your case we want to Join the two tables and we can do that using an INNER JOIN on your id_customer field that is common between the two tables. We can turn that into it's own table if you want to hold, permanently, those results. It would look something like

我们没有“向表中添加列”。相反,我们将SQL提交给返回我们想要的结果集的数据库。在您的情况下,我们想要加入这两个表,我们可以使用id_customer字段上的INNER JOIN来实现这一点,这两个表在两个表之间是通用的。如果你想永久保留那些结果,我们可以把它变成自己的表。它看起来像

SELECT 
    orders.id_order,
    orders.reference,
    orders.id_shop_group,
    orders.id_shop,
    orders.id_carrier,
    orders.id_lang,
    orders.id_customer,
    orders.id_cart,
    customer.id_gender,
    customer.firstname,
    customer.lastname
FROM orders INNER JOIN customer on orders.id_customer = customer.id_customer;

You can tweak the list of fields to be returned from the joining of these tables to suit your needs.

您可以调整从这些表的连接返回的字段列表,以满足您的需要。

#2

The fact that id_shop and id_shop_group are in both tables suggests they are part of a composite key. You may need to join using all three shared columns to guarantee unique rows. Otherwise you may retrieve duplicate order rows where the customer belongs to more than one shop.

id_shop和id_shop_group在两个表中的事实表明它们是复合键的一部分。您可能需要使用所有三个共享列进行连接以保证唯一行。否则,您可以检索客户属于多个商店的重复订单行。

e.g.

SELECT
...
FROM orders INNER JOIN customer on orders.id_customer = customer.id_customer 
and orders.id_shop_group = customer.id_shop_group
and orders.id_shop = customer.id_shop