?
Solved

OUTER JOIN not working

Posted on 2014-02-20
5
Medium Priority
?
299 Views
Last Modified: 2014-02-21
I am trying to find missing records in a table, but the outer join is not working

I have two source tables that give me a set of all results:
ID_COL      REC_TYPE
B12345      TYPE_A
B12345      TYPE_B
B12346      TYPE_A
B12347      TYPE_B
B12348      TYPE_A

And another Table that gives me a set of values:
ID_COL      REC_TYPE      DATA_VAL      CHANGE_DT
B12345      TYPE_A      45.0000      2014-01-06 09:03:00.620
B12345      TYPE_B      23.0000      2014-01-28 09:03:00.620
B12346      TYPE_A      43.0000      2014-01-08 09:03:00.620
B12347      TYPE_B      54.0000      2013-12-28 09:03:00.620

I am trying to fin the missing record; in this case B12348  TYPE_A

But my query doesn't seem to work:
SELECT tr.ID_COL, tr.REC_TYPE
FROM TestTableNames tn
JOIN TestTableRecs tr on tr.ID_COL = tn.ID_COL
LEFT OUTER JOIN TestTable1 t1 on t1.ID_COL = tr.ID_COL and t1.REC_TYPE = tr.REC_TYPE

Open in new window


I get all the possible or the same as just the:
SELECT tr.ID_COL, tr.REC_TYPE
FROM TestTableNames tn
JOIN TestTableRecs tr on tr.ID_COL = tn.ID_COL

Open in new window


What am I doing wrong?
0
Comment
Question by:GNiessen
[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
5 Comments
 
LVL 8

Accepted Solution

by:
5teveo earned 750 total points
ID: 39873717
Try - should pulling only NULL row...

SELECT tr.ID_COL, tr.REC_TYPE
FROM TestTableNames tn
JOIN TestTableRecs tr on tr.ID_COL = tn.ID_COL
Where
tr.REC_TYPE is null
0
 

Author Comment

by:GNiessen
ID: 39873836
Well, your example is not referencing the TestTable1.

But applying that idea I got:

SELECT tr.ID_COL, tr.REC_TYPE
FROM TestTableNames tn
JOIN TestTableRecs tr on tr.ID_COL = tn.ID_COL
LEFT OUTER JOIN TestTable1 t1 on t1.ID_COL = tr.ID_COL and t1.REC_TYPE = tr.REC_TYPE
WHERE t1.REC_TYPE IS NULL

Which does seem to work.  

I'll Test it further.
0
 
LVL 8

Expert Comment

by:5teveo
ID: 39873873
Ah...

Yeah I copy and pasted wrong example code from your description...

Good Luck.
0
 

Author Closing Comment

by:GNiessen
ID: 39876637
Good concept, bad example.  Solution works.
0
 
LVL 8

Expert Comment

by:5teveo
ID: 39876755
Thanks for points... Good luck
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

752 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