?
Solved

Left Outer join in sql server

Posted on 2009-04-10
8
Medium Priority
?
774 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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
 
LVL 60

Accepted Solution

by:
chapmandew earned 1600 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:udaya kumar laligondla
udaya kumar laligondla earned 400 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

800 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