我只想问一下如何合并两个表.
因为我有一位餐桌客人,所以他们俩的公司都得到了预订.
我想同时加入他们和将他们标记为客户,并且我想添加一个新的列客户类型,如果我的公司为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.
更多推荐
数据库合并表
发布评论