| Author |
 Topic  |
|
|
U A
Starting Member
India
11 Posts |
 Posted - 11/13/2010 : 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
United Kingdom
22191 Posts |
Posted - 11/13/2010 : 09:08:59
|
| How do you know which Phone/Address in Table2 goes with which ID/Name in Table1? |
 |
|
|
ahmeds08
Constraint Violating Yak Guru
India
430 Posts |
Posted - 11/14/2010 : 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
India
11 Posts |
Posted - 11/14/2010 : 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
Constraint Violating Yak Guru
India
430 Posts |
Posted - 11/15/2010 : 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
Constraint Violating Yak Guru
India
430 Posts |
Posted - 11/15/2010 : 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
/***********************************************************************************/
|
|
|
| |
Topic |
|
|
|
How to Join
