Left Outer join in sql server
i have 2 table tbl1 , tbl2
The keys in tbl1,tbl2 are (col1,col2,col3)
tbl1 has the follwoing data
col1 col2 col3 col4
--------------------------
10 8 1 x
10 8 2 m
10 8 3 n
The tbl2 has data as following
col1 col2 col3
--------------------------
10 8 1
10 8 2
When i use Left outer join as following
From tbl1 Left outer join Tbl2 ON
Tbl1.col1=tbl2.col1 and tbl1.col2=tbl2.col2 and tbl1.col3=tbl2.col3
the result comes with 2 record
10 8 1 x
10 8 2 m
What i need is i get all records from tbl1 regardless if it exist in tble2 or not BUT I must use the 3 columns in the join
How can i do that ?
The keys in tbl1,tbl2 are (col1,col2,col3)
tbl1 has the follwoing data
col1 col2 col3 col4
--------------------------
10 8 1 x
10 8 2 m
10 8 3 n
The tbl2 has data as following
col1 col2 col3
--------------------------
10 8 1
10 8 2
When i use Left outer join as following
From tbl1 Left outer join Tbl2 ON
Tbl1.col1=tbl2.col1 and tbl1.col2=tbl2.col2 and tbl1.col3=tbl2.col3
the result comes with 2 record
10 8 1 x
10 8 2 m
What i need is i get all records from tbl1 regardless if it exist in tble2 or not BUT I must use the 3 columns in the join
How can i do that ?
chapmandew
You're doing it correctly. Does your select statement have a where clause?
ASKER
Hi chapmandew:
no i didnt use any where claues
no i didnt use any where claues
something is missing because your join is correct.
ASKER
chapmandew:
But note that am using tbl1.col3=tbl2.col3 in JOIN , so the row
10 8 3 n
will not find any row in table 2 has the same values
so how this row will appear ?
But note that am using tbl1.col3=tbl2.col3 in JOIN , so the row
10 8 3 n
will not find any row in table 2 has the same values
so how this row will appear ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The symptoms you have mentioned sound like you are selecting from Table2 instead of Table1 in you query. You should select the 3 columns you are using for the join (and any others that you need that are only in TABLE1) from Table1 and then only those columns that you _might_ need (if the records exist) from Table2. (See SQL_1 below)
SELECT Tbl1.col1,
tbl1.col2,
tbl1.col3
tbl1.othercolumns,
Tbl2.somecolumns
FROM Tbl1
LEFT OUTER JOIN Tbl2
ON Tbl1.col1=tbl2.col1 AND
tbl1.col2=tbl2.col2 AND
tbl1.col3=tbl2.col3
Oops, forgot a comma again. ;-)
SELECT Tbl1.col1,
tbl1.col2,
tbl1.col3,
tbl1.othercolumns,
Tbl2.somecolumns
FROM Tbl1
LEFT OUTER JOIN Tbl2
ON Tbl1.col1=tbl2.col1 AND
tbl1.col2=tbl2.col2 AND
tbl1.col3=tbl2.col3