Solved

Left Outer join in sql server

Posted on 2009-04-10
8
760 Views
Last Modified: 2012-05-06
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 ?

0
Comment
Question by:ali_alannah
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24115337
You're doing it correctly.  Does your select statement have a where clause?
0
 

Author Comment

by:ali_alannah
ID: 24115345
Hi chapmandew:
no i didnt use any where claues
 
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24115351
something is missing because your join is correct.
0
 

Author Comment

by:ali_alannah
ID: 24115401
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 60

Accepted Solution

by:
chapmandew earned 400 total points
ID: 24115428
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
 
LVL 12

Assisted Solution

by:udayakumarlm
udayakumarlm earned 100 total points
ID: 24115456
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24115668
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24115675
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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now