Solved

OUTER JOIN not working

Posted on 2014-02-20
5
283 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 250 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

761 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