Solved

OUTER JOIN not working

Posted on 2014-02-20
5
274 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
  • 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now