CODEWITHSUNDEEP
mysqldatabasemerge
rj tubera
rj tubera

Reputation: 757

Database Merging Tables

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>

Upvotes: 1

Views: 176

Answers (2)

nerdherd
nerdherd

Reputation: 2603

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.

Upvotes: 2

Chuck Rostance
Chuck Rostance

Reputation: 7104

Not sure the schema is the best but consider the following tables:

Res (resId, description, cid, gid, details)

1   Guest Res   1   NULL    Just some guy
2   Comp Res    NULL    1   Corporate jerk

Guest (gid, name)

1   Chuck

Company (cid, name)

1   Tenth

Check out the parameters of the select query:

1. r.rid : id of reservation table
2. isnull(c.name, g.name) : gets the non null value from either company or guest
3. isnull(replace(c.name, c.name, 'Company'), 'Guest') as type
    Fanciness...if c.name is something, then 'Company', otherwise 'Guest'
4. r.details : reservation details

You need the outer join because of the nulls, this gives you a complete view of your data.

left outer join company c on r.cid = c.cid
left outer join guest g on r.gid = g.gid

So if you stick it all together you get what you are looking for:

select r.rid, r.description, isnull(c.name, g.name), 
isnull(replace(c.name, c.name, 'Company'), 'Guest') as type, 
r.details from res r
left outer join company c on r.cid = c.cid
left outer join guest g on r.gid = g.gid

1   Guest Res   Chuck   Guest   Just some guy
2   Comp Res    Tenth   Company Corporate jerk

Upvotes: 2

Related Questions