Solved

Left Outer join in sql server

Posted on 2009-04-10
8
772 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
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 

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 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:udaya kumar laligondla
udaya kumar laligondla 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

691 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