数据库合并表 [英] Database Merging Tables

问题描述

我只想问一下如何合并两个表.

因为我有一位餐桌客人，所以他们俩的公司都得到了预订.

我想同时加入他们和将他们标记为客户，并且我想添加一个新的列客户类型，如果我的公司为null，则为guest；如果我的客户为null，则为company.... /p>

这是一个例子:

guest(g_id(PK), name, guest details etc....)
company(c_id(PK), name, company details etc...)
reservation(r_id(PK), g_id(fk), c_id(fk), reservation details etc...)

当我加入他们的时候(假设客人已经预订了房间)，我的桌子看起来像是

reservation_id|company name|guest name|reservation details
     1           null      <name>     <details>
     2           null      <name>     <details>

我要使它像这样:

reservation_id|customer name|cust_type|reservation details
     1          <name>      <guest>     <details>
     2          <name>      <company>     <details>

解决方案

一种可能的解决方案是，首先为每种类型的预订创建一个查询(您可能已经拥有)，然后在其中添加cust_type，然后将两者合并在一起.因此初始查询将类似于:

客人预订:

SELECT reservation.r_id, guest.name, "guest" AS cust_type, {other fields}
FROM guest, reservation INNER JOIN guest ON guest.g_id = reservation.g_id;

company_reservations:

SELECT reservation.r_id, company.name, "company" AS cust_type {other fields}
FROM company, reservation INNER JOIN company ON company.c_id = reservation.c_id;

然后，您可以将两个查询合并在一起，如下所示.

SELECT reservation.r_id, guest.name, "guest" AS cust_type, {other fields}
FROM guest, reservation INNER JOIN guest ON guest.g_id = reservation.g_id
UNION SELECT reservation.r_id, company.name, "company" AS cust_type {other fields}
FROM company, reservation INNER JOIN company ON company.c_id = reservation.c_id;

当然，请记住要删除{other fields}或在其中添加其他任何内容.

I just want to ask how to merge two tables.

Because I have a table guest and company both of them acquire a reservation.

I want to join both of them and label them as customers and I want to add a new column customer type which will say guest, if my company is null... and company, if my guest is null...

Here's an example:

guest(g_id(PK), name, guest details etc....)
company(c_id(PK), name, company details etc...)
reservation(r_id(PK), g_id(fk), c_id(fk), reservation details etc...)

When I join them (assuming guest has acquired a reservation) my table looks like

reservation_id|company name|guest name|reservation details
     1           null      <name>     <details>
     2           null      <name>     <details>

What I want is to make it like this:

reservation_id|customer name|cust_type|reservation details
     1          <name>      <guest>     <details>
     2          <name>      <company>     <details>

解决方案

One possible solution would be to first create a query for each type of reservation (which you might already have) and add the cust_type there, then union the two together. So the initial queries would be something like:

guest_reservations:

SELECT reservation.r_id, guest.name, "guest" AS cust_type, {other fields}
FROM guest, reservation INNER JOIN guest ON guest.g_id = reservation.g_id;

company_reservations:

SELECT reservation.r_id, company.name, "company" AS cust_type {other fields}
FROM company, reservation INNER JOIN company ON company.c_id = reservation.c_id;

Then you could union the two queries together, something like below.

SELECT reservation.r_id, guest.name, "guest" AS cust_type, {other fields}
FROM guest, reservation INNER JOIN guest ON guest.g_id = reservation.g_id
UNION SELECT reservation.r_id, company.name, "company" AS cust_type {other fields}
FROM company, reservation INNER JOIN company ON company.c_id = reservation.c_id;

Of course remember to either remove {other fields} or add in whatever else you need there.

这篇关于数据库合并表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！