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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to Join

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, Name

Table 2:
Phone, Address

I want to create a view to produce the data as like:

Table:
ID, NAme, Phone, Address

pls 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?
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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_MAPPING
LEFT JOIN TABLE1
ON TABLE1.ID=TABLE1TABLE2_MAPPING.ID
LEFT JOIN TABLE2
ON TABLE2.TABLE2_ID=TABLE1TABLE2_MAPPING.TABLE2_ID
/***********************************************************************************/
Go to Top of Page
   

- Advertisement -