Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

OUTER JOIN not working

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
GNiessen
Asked:
GNiessen
  • 3
  • 2
1 Solution
 
5teveoCommented:
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
 
GNiessenAuthor Commented:
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
 
5teveoCommented:
Ah...

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

Good Luck.
0
 
GNiessenAuthor Commented:
Good concept, bad example.  Solution works.
0
 
5teveoCommented:
Thanks for points... Good luck
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now