SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to Join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

U A
Starting Member

India
11 Posts

Posted - 11/13/2010 :  05:50:17  Show Profile  Reply with Quote
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
22403 Posts

Posted - 11/13/2010 :  09:08:59  Show Profile  Reply with Quote
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

India
583 Posts

Posted - 11/14/2010 :  01:38:33  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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

India
11 Posts

Posted - 11/14/2010 :  03:12:30  Show Profile  Reply with Quote
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

India
583 Posts

Posted - 11/15/2010 :  03:50:19  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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

India
583 Posts

Posted - 11/15/2010 :  04:15:52  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000