Union Query Gives Odd Results

[grnzbra]

I have the following code

SELECT fldChecklistKey, fldOfficeKey, fldChecklistCode
FROM SourceDB.dbo.tblChecklist
WHERE fldOfficeKey = @OfficeKey
AND ISNULL(fldDeleteInd,0)= 0
UNION
SELECT fldChecklistKey, fldOfficeKey, fldChecklistCode
FROM TargetDB.dbo.tblCheckList
WHERE fldOfficeKey = @OfficeKey
AND ISNULL(fldDeleteInd,0) = 0
ORDER BY fldChecklistCode

SELECT *
FROM TargetDB.dbo.tblChecklist
WHERE fldChecklistCode = 'DHRC01AN'

The result of running these two is (ignore hyphens - just used them to align the columns)

997A5356-1407-42FF-AFCC-EFB041DDC684 - 85DDE31C-E169-46A2-BF10-741EE1039E06 - CH01AN
23BE1EF9-80BE-4FB9-99F7-3C302ED258CB - 85DDE31C-E169-46A2-BF10-741EE1039E06 - DHRC01AN
10AA18D2-7C3C-423F-8090-76A97D6D9303 - 85DDE31C-E169-46A2-BF10-741EE1039E06 - DP01AN

23BE1EF9-80BE-4FB9-99F7-3C302ED258CB - E382085E-C1ED-46D3-9D2B-1B8B8BFD7541 - DHRC01AN

The problem is shown in the second query. @OfficeKey is equal to the 85DDE31C... value. However, as show by the simple second query, there is no Checklist Code DHRC01AN with an 85DDE31C... value in the TargetDB. It is known that only 85DDE31C... office keys exist in the SourceDB. How can I fix the union query so that only 85DDE31C... office keys result. The union query is giving 64 records, only 20 of which are from 85DDE31C... office.

 

[SkipVought]

So what do you get from...

SELECT fldChecklistKey, fldOfficeKey, fldChecklistCode
FROM TargetDB.dbo.tblCheckList
WHERE fldOfficeKey = 'DHRC01AN'
AND ISNULL(fldDeleteInd,0) = 0
ORDER BY fldChecklistCode

SELECT *
FROM TargetDB.dbo.tblChecklist
WHERE fldChecklistCode = 'DHRC01AN'

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[bborissov]

You have ORDER BY clause in your UNION query, so maybe
this:
[tt]
23BE1EF9-80BE-4FB9-99F7-3C302ED258CB - 85DDE31C-E169-46A2-BF10-741EE1039E06 - DHRC01AN
[/tt]
is from SourceDB.dbo.tblChecklist

Borislav Borissov
VFP9 SP2, SQL Server

 

[grnzbra]

Thank you both for your patience. I made two invalid assumptions. My instant first assumption was that there was something wrong with my UNION query, and second was that nothing had been added to the SourceDB after I loaded it. Indeed, all of the "extraneous" records are coming from the source db.