how to join three tables using sp or Text query in sql [Resolved]

Posted by Gow.Net under Sql Server on 8/19/2012 | Points: 10 | Views : 1845 | Status : [Member] | Replies : 5
hi friends i attach my source here i need how to join three tables using sp or Text query in sql


create table city(C_ID int primary key identity,City_Name varchar(30))

create table Area(A_ID int primary key identity,Area_Name varchar(30),C_ID int )

create table UserSearch(U_SID int primary key identity,userKeyword varchar(50),C_ID int ,A_ID int )



here
city,Area,UserSearch its all table name,this table contain common field in C_ID based on this id how to join this table


please any one teach me

gowthaman8870226416


Responses

Posted by: Pandians on: 8/19/2012 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Select C.City_Name, A.Area_Name, US.userKeyword from city C With(Nolock) 

Join Area A With(Nolock) On (C.C_ID = A.C_ID)
Join UserSearch US With(Nolock) On (US.C_ID = A.C_ID And A.A_ID = US.A_ID)
Go


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mrashish77 on: 8/20/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
SELECT city.C_ID,city.City_Name,Area.A_ID, Area.Area_Name , UserSearch.U_SID,UserSearch.userKeyword
FROM UserSearch With(Nolock)
INNER JOIN Area With(Nolock) ON UserSearch.A_ID = Area.A_ID
INNER JOIN city With(Nolock) ON UserSearch.C_ID = city.C_ID

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 8/20/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

Try this simple query


Select City_Name,Area_Name,userKeyword
from city C
left Join Area A On C.C_ID = A.C_ID
left Join UserSearch U On C.C_ID = U.C_ID


Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gow.Net on: 8/20/2012 [Member] Starter | Points: 25

Up
0
Down
hi Karthiga
i got so much duplicate values i can't i get exact output can you give me other solution

gowthaman8870226416

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 8/21/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

Well, you got duplicate values because Area and UserSearch tables not having unique C_ID.
You can use this query for your exact result

Select City_Name,Area_Name,userKeyword
from city C
left Join Area A On C.C_ID = A.C_ID
left Join UserSearch U On C.C_ID = U.C_ID


Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response