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 ?

ali_alannahAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
Im aware....it shouldn't matter since you're doing a left join.  A left join says, show me all rows from tableA and only the rows from tableB where the rows where the values from TableB match up w/ those in TableA

post your entire query...everything.
0
 
chapmandewCommented:
You're doing it correctly.  Does your select statement have a where clause?
0
 
ali_alannahAuthor Commented:
Hi chapmandew:
no i didnt use any where claues
 
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
chapmandewCommented:
something is missing because your join is correct.
0
 
ali_alannahAuthor Commented:
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 ?
 
0
 
udaya kumar laligondlaConnect With a Mentor Technical LeadCommented:
can you post the actual tables and query. your query can be replaced with
select Col1,Col2,Col3,Col4 from tab1.
you are looking for all the columns in table1 only and no columns of table2 are used. with left outer join the effect of the query you are using and using
select Col1,Col2,Col3,Col4 from tab1 will be same and it will be more effective if you don;t use the join condition.
0
 
8080_DiverCommented:
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

Open in new window

0
 
8080_DiverCommented:
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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.