5 Replies - 457 Views - Last Post: 17 June 2017 - 08:22 AM

#1 user1825  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 04-June 17

how to merge two table with different columns in sql

Posted 17 June 2017 - 03:52 AM

I have two table named as booked_flat and master_flat.

columns of booked_flats are

customer_name, customer_address, customer_phone, building_wing_no, building_name_no, flat_no
columns of master_flats are

building_wing_no, building_name_no, flat_no, status
when any flat get booked the status get change as sold else status remains unsold.

all entries with sold status are in booked_flats.

I have tried to merge this two table but it gives an error please help me

$sql =  SELECT * FROM booked_flats 
        UNION SELECT * FROM master_flats 
            WHERE building_wing_no, building_name_no, flat_no NOT IN (
                SELECT building_wing_no, building_name_no, flat_no from booked_flats);

example:

master_flat:

 building_wing_no building_name_no flat_no status            
 -----------------------------------------------
     a                a2            104   unsold 
 -------------------------------------------------
     b                a3            105    sold





 booked_flat:
  custo_name custo_add custo_ph building_wing_no  building_name_no flat_no
  --------------------------------------------------------------------------- 
   harish     wardha     284632        b              a3           105




I want result like

  cust_name cust_add custo_ph building_wing_no  building_name_no flat_no status   
     harish    wardha    284632         b            a3             105            
      ---       ---       ---            a           a2             104   unsold

This post has been edited by modi123_1: 17 June 2017 - 08:22 AM
Reason for edit:: Added code tags to the table


Is This A Good Question/Topic? 0
  • +

Replies To: how to merge two table with different columns in sql

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13560
  • View blog
  • Posts: 54,109
  • Joined: 12-June 08

Re: how to merge two table with different columns in sql

Posted 17 June 2017 - 04:10 AM

Use a JOIN if you want to combine two different tables off one to many keys.
Was This Post Helpful? 0
  • +
  • -

#3 user1825  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 04-June 17

Re: how to merge two table with different columns in sql

Posted 17 June 2017 - 04:28 AM

I used join but it gives error. i want to display some entries from another table so in this case join didnt give desired result.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13560
  • View blog
  • Posts: 54,109
  • Joined: 12-June 08

Re: how to merge two table with different columns in sql

Posted 17 June 2017 - 04:30 AM

FYI - I can't help if I don't know what the error messages is.

I am not tracking then - what extra information are you wanting to get?

Union simply won't work if there are not the same columns.
Was This Post Helpful? 0
  • +
  • -

#5 user1825  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 04-June 17

Re: how to merge two table with different columns in sql

Posted 17 June 2017 - 08:08 AM

it provides an error when i join this two tables with union as it display syntax error

I am not getting how to join this two queries

please help me to sort this query.. thank you
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13560
  • View blog
  • Posts: 54,109
  • Joined: 12-June 08

Re: how to merge two table with different columns in sql

Posted 17 June 2017 - 08:22 AM

So much jacked up.

Your customer name/info should *NOT* be mixed with booking info. You would have a third table matching customer id to booking id. Then.. THEN you join to get that last table, etc. 'flat status' is implied if it is in that third table or not.

With a join.

cr eate table #customer (ID int, name varchar(20))
cr eate table #rooms		(id int, number varchar(10))
cr eate table #booked	 (id INT, customerID int, roomID int, DATE_ENTERED datetime)

ins ert into #customer(id, name) values (1, 'aaaa')
ins ert into #customer(id, name) values (2, 'bbbb')
in sert into #customer(id, name) values (3, 'cccc')

in sert into #rooms (id, number) values (1, 'a1')
in sert into #rooms (id, number) values (2, 'a2')
in sert into #rooms (id, number) values (3, 'b1')
ins ert into #rooms (id, number) values (4, 'b2')

ins ert into #booked(id, customerID, roomID, DATE_ENTERED) values (1, 1, 1, '06/17/2017')
ins ert into #booked(id, customerID, roomID, DATE_ENTERED) values (1, 2, 3, '04/27/2017')
in sert into #booked(id, customerID, roomID, DATE_ENTERED) values (1, 3, 4, '05/1/2017')

-- what is in ther
 select * from #customer
 select * from #rooms		
 select * from #booked	

-- want the customer name, room info, and when it was entered.
SELECT a.*, c.*, b.DATE_ENTERED
from #customer a 
join #booked b on a.ID = b.customerID
join #rooms c on b.roomID = c.id


d rop table #booked 
d rop table #rooms 
d rop table #customer 



-- the data entered
ID          name
----------- --------------------
1           aaaa
2           bbbb
3           cccc



id          number
----------- ----------
1           a1
2           a2
3           b1
4           b2



id          customerID  roomID      DATE_ENTERED
----------- ----------- ----------- -----------------------
1           1           1           2017-06-17 00:00:00.000
1           2           3           2017-04-27 00:00:00.000
1           3           4           2017-05-01 00:00:00.000



The output wanted
ID          name                 id          number     DATE_ENTERED
----------- -------------------- ----------- ---------- -----------------------
1           aaaa                 1           a1         2017-06-17 00:00:00.000
2           bbbb                 3           b1         2017-04-27 00:00:00.000
3           cccc                 4           b2         2017-05-01 00:00:00.000

(3 row(s) affected)


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1