Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
U A
Starting Member
11 Posts |
Posted - 2010-11-13 : 05:50:17
|
How can we join two tables which having no common column. For ref:Table 1:ID, NameTable 2:Phone, AddressI want to create a view to produce the data as like:Table:ID, NAme, Phone, Addresspls help me regarding this.U A |
|
Kristen
Test
22859 Posts |
Posted - 2010-11-13 : 09:08:59
|
How do you know which Phone/Address in Table2 goes with which ID/Name in Table1? |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2010-11-14 : 01:38:33
|
u cannot join unless u have a common column.but u can do one thing.create a mapping table.from there u can retrieve the records as u want.if iam not wrong. |
|
|
U A
Starting Member
11 Posts |
Posted - 2010-11-14 : 03:12:30
|
Mapping table means , i dont know about mapping table please explain this quote: Originally posted by ahmeds08 u cannot join unless u have a common column.but u can do one thing.create a mapping table.from there u can retrieve the records as u want.if iam not wrong.
U A |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2010-11-15 : 03:50:19
|
a mapping table has both the primary keys of your two tables.in which every record from table 1 maps to the records of table 2.it may have one to one relation or one to many relation.if you still didnt get me i will explain it with an example query. |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2010-11-15 : 04:15:52
|
please run this query.it may clear your doubt./***SCRIPT FOR CREATING YOUR REQUIRED TABLES******************************/CREATE TABLE TABLE1(ID INT PRIMARY KEY,NAME VARCHAR(20))/**INSERTING DATA INTO TABLE1**************/INSERT INTO TABLE1(ID,NAME)VALUES(1,'NAME1')INSERT INTO TABLE1(ID,NAME)VALUES(2,'NAME2')INSERT INTO TABLE1(ID,NAME)VALUES(3,'NAME3')------------------------------------------------------------------------CREATE TABLE TABLE2(TABLE2_ID INT PRIMARY KEY,PHONE VARCHAR(15),ADDRESS VARCHAR(20))/**INSERTING DATA INTO TABLE2**************/INSERT INTO TABLE2(TABLE2_ID,PHONE,ADDRESS)VALUES(1,'23242423','DELHI')INSERT INTO TABLE2(TABLE2_ID,PHONE,ADDRESS)VALUES(2,'6745545','MUMBAI')INSERT INTO TABLE2(TABLE2_ID,PHONE,ADDRESS)VALUES(3,'64564748','HYDERABAD')------------------------------------------------------------------------CREATE TABLE TABLE1TABLE2_MAPPING(TABLE1TABLE2_MAPPING_ID INT PRIMARY KEY,ID INT,TABLE2_ID INT)/**IN TABLE1TABLE2_MAPPING TABLE ID IS THE PRIMARY KEY OF TABLE1 AND TABLE2_ID IS THE PRIMARY KEY OF TABLE2**//**INSERTING DATA INTO TABLE1TABLE2_MAPPING**************/INSERT INTO TABLE1TABLE2_MAPPING(TABLE1TABLE2_MAPPING_ID,ID,TABLE2_ID)VALUES(1,1,1)INSERT INTO TABLE1TABLE2_MAPPING(TABLE1TABLE2_MAPPING_ID,ID,TABLE2_ID)VALUES(2,1,2)INSERT INTO TABLE1TABLE2_MAPPING(TABLE1TABLE2_MAPPING_ID,ID,TABLE2_ID)VALUES(3,2,2)INSERT INTO TABLE1TABLE2_MAPPING(TABLE1TABLE2_MAPPING_ID,ID,TABLE2_ID)VALUES(4,3,3)/***************************************************************************//*****QUERY FOR GETTING THE REQUIRED RESULT***************************************/SELECT TABLE1.ID,TABLE1.NAME,TABLE2.PHONE,TABLE2.ADDRESS FROM TABLE1TABLE2_MAPPINGLEFT JOIN TABLE1ON TABLE1.ID=TABLE1TABLE2_MAPPING.IDLEFT JOIN TABLE2ON TABLE2.TABLE2_ID=TABLE1TABLE2_MAPPING.TABLE2_ID/***********************************************************************************/ |
|
|
|
|
|
|
|