SQL Server - Problem with outer join - Asked By mostafa hamdy on 16-Nov-11 10:40 AM

Hello All
I have some database contains 4 tables and I make outer join b/w them the schema of them is as the following:
USERTable

Name
ID
Sex
Nationality

Table2
ID
UserID
NickName

Table3
ID
UserID
Position

Table4
ID
UserID
Country

I write come query like the following:
SELECT ID, Name,Nationality,t1.nickName,t2.Position,t3.Country
FROM userTable  Left outer join Table1 t1 on Usertable.ID=Table1.UserID
Left outer join Table2 t2 on Usertable.ID=Table2.UserID
Left outer join Table3 t3 on Usertable.ID=Table3.UserID

Table1,Table2 and table3 may conatins more than one value for every user means user may have more than one nickname and position, this makes the result returns more than one record for one user
I just want to search with the nick , position and country and return only one record for user and also return one nickname, and one Position ...etc
please if any body get what I mean and can help me please send me or tell em about some URL may help me in doing that
regards
Mostafa



Robbe Morris replied to mostafa hamdy on 16-Nov-11 09:25 PM
What value in Table1, Table2, or Table3 would dictate "which" record you prefer to get back?  If SQL Server returns more than one record, do you expect it to guess which record is the right one to return?
sanjeev katiyar replied to Robbe Morris on 17-Nov-11 12:58 AM

Hi,

  fire the query in a sequence :
   1) create table t1 as select distinct(user_id) from table1
2)create table t2 as select distinct(user_id) from table2
3)create table t3 as select distinct(user_id) from table3

   SELECT ID, Name,Nationality,t1.nickName,t2.Position,t3.Country
FROM userTable  Left outer join t1 on Usertable.ID=t1.UserID
Left outer join  t2 on Usertable.ID=t2.UserID
Left outer join t3 on Usertable.ID=t3.UserID

use this query logic
-------------------------------------------------------------------------------------------------------



USERTable

Name
ID
Sex
Nationality

Table2
ID
UserID
NickName

Table3
ID
UserID
Position

Table4
ID
UserID
Country

I write come query like the following:
SELECT ID, Name,Nationality,t1.nickName,t2.Position,t3.Country
FROM userTable  Left outer join Table1 t1 on Usertable.ID=Table1.UserID
Left outer join Table2 t2 on Usertable.ID=Table2.UserID
Left outer join Table3 t3 on Usertable.ID=Table3.UserID

Table1,Table2 and table3 may conatins more than one value for every user means user may have more than one nickname and position, this makes the result returns more than one record for one user
I just want to search with the nick , position and country and return only one record for user and also return one nickname, and one Position ...etc