TOAD, SQL SERVER, determine the missing ID's difference b/w two files

Toad Data Point
1

Hi

I have two files each with one column, which is a simple unique ID.

One file is a subset of 1,196 IDs from the larger file of 1,504 IDs, the difference = 308.

I am simply trying to determine which 308 IDs are missing from the original file.

I have tried two methods,

Method 1:

SELECT * FROM RiskAdjust_Temp.dbo.PROF_ID_Tmp /1,196/
MINUS
SELECT * FROM RiskAdjust_Temp.dbo.PROF_ID_Tmp1; /1,504/

In that method I have reversed the tmp files and the results bring back either the 1,196 IDs or I get all 1,504 IDs returned.

Method 2:

SELECT *

INTO RiskAdjust_Temp.dbo.PROF_ID_Tmp1

FROM RiskAdjust_Temp.dbo.WHP_CPP_PROF_CCs a

WHERE a.“Prof Mem ID” not in (select “Prof Mem ID” from RiskAdjust_Temp.dbo.PROF_ID_Tmp b);

SELECT COUNT() AS NumberOf FROM RiskAdjust_Temp.dbo.PROF_ID_Tmp1; /= 0 something wrong*/

In that method I am getting back 0 IDs.

Any ideas or suggestions would be most appreciated.

Thanks

WHP

2

Have you tried a simple select with an outer join? In the minus query, try selecting just the one column and not *.

Also, be sure the data type is the same. If one is a varchar and the other uniqueID, for example, you might be running into some mismatching problems.

3

Thank you itlnstln.

I tried a left join with no luck, returns the original number of records.

Using the term outer join produces an error.

SELECT a.“Prof Mem ID”

INTO RiskAdjust_Temp.dbo.PROF_ID_Tmp2

FROM RiskAdjust_Temp.dbo.PROF_ID_Tmp a /1,196/

OUTER JOIN RiskAdjust_Temp.dbo.PROF_ID_Tmp1 b /1,504/
on a.“Prof Mem ID” = b.“Prof Mem ID”

GROUP BY a.“Prof Mem ID”
ORDER BY a.“Prof Mem ID”;

The columns are formatted the same since one file is a subset of the other.

And I have tried replacing the * with the actual column name and that did not help.

WHP

4

Thank you itlnstln, once I wrote the syntax properly your suggestion worked, thanks.

WHP

SELECT a.“Prof Mem ID” as “Prof Mem IDTMP”
,b.“Prof Mem ID” as “Prof Mem IDTMP1”

INTO RiskAdjust_Temp.dbo.PROF_ID_Tmp2

FROM RiskAdjust_Temp.dbo.PROF_ID_Tmp1 b /1,504/

LEFT JOIN RiskAdjust_Temp.dbo.PROF_ID_Tmp a /1,196/
on b.“Prof Mem ID” = a.“Prof Mem ID”

GROUP BY a.“Prof Mem ID”, b.“Prof Mem ID”
ORDER BY a.“Prof Mem ID”;

SELECT COUNT() AS NumberOf FROM RiskAdjust_Temp.dbo.PROF_ID_Tmp2; /= 1,504 and 1,196 so the missing 308 are evident now*/

SELECT a.“Prof Mem IDTMP1”

INTO RiskAdjust_Temp.dbo.PROF_ID_Tmp3

FROM RiskAdjust_Temp.dbo.PROF_ID_Tmp2 a

WHERE a.“Prof Mem IDTMP” is null;

SELECT COUNT() AS NumberOf FROM RiskAdjust_Temp.dbo.PROF_ID_Tmp3;/= 308*/