Solved

OUTER JOIN not working

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 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

21 Experts available now in Live!

Get 1:1 Help Now