(Microsoft) SQL wierdness

[iddu]

Left join query not returing *field content* of outer joined table...

select *
from ACO_CCaseContact as CCC
left outer join ACO_Organisation as O on CCC.OrganisationID = O.OrganisationID
where CCC.SID = 2 and CCC.Case_ID = 'EXP-0200860' and CCC.CIdx = 8000

returns 1 row, but the fields O.* are all null (i.e. thinks join could not be constructed).

OrganisationID can both be proved by dump of field values to be equivalent value  of "0x4141414141465246545532526B346E4641796E5743436B6F553733667445" (char field in hex)

No, it's not use of O.* in where clause causing implict inner join/filtering of row, unless I'm missing something.

Any clues?

[FifeingEejit]

Are both OrganizationId fields the same data type?
Is there a foreign key constraint on CCC.OrganizationId for O.OrganizationId?
Whats the execution plan looking like?

[iddu]

Yes
No
As to be expected - nothing anomolous jumps out.

Just doesn't seem to believe the relevant value can be found in any row of O. - so 'correctly' fails the join, whilst reviewing the O. table content singularly does show existence.

[iddu]

Relevant CheckDB  already run w/o issues, so apparantly DB content in good nick...

[FifeingEejit]

Stating the obvious: The lack of foreign key constraint allows for the potential of the data to be different while apparently not on visual inspection.

What's the data type?
If it's VARCHAR what's the Collition of both columns?

It's sounding like it's one of those situations where the difference is so blindingly obvious that you can't see it for the glare, or ridiculously pernickety.


I'm currently lost in ORACLEland...
Seriously lost, no one ever thought about how they were managing these databases and certainly not before merging them... and now we're trying to shift them to a version 12 from a mess of 10s and 8s...
Currently riding the Circular dependency roundabout

[Chris S]

Don't you usually get an error if you try and equate columns of different collation?

[iddu]

Collation both fields is SQL_Latin1_General_CP1_CI_AS, and yes, you'd (generally) get an error on mismatch

> It's sounding like it's one of those situations where the difference is so blindingly obvious that you can't see it for the glare, or ridiculously pernickety.

Yeerrrz - I've already tried explaining it to the cat, for the DOH! moment.

[iddu]

char(88) on both sides, singular field usage as PKey in O table.

[iddu]

Grrrr...

FFS.   

Pad one field with LF.  Pad other with space. Equal in the JOIN? - Are they bollocks. 
Can you see it in grid / via cast to varbinary - No.  Gotta use [sys].[fn_varbintohexstr](...)

Gonna be a severe LART'ing tomorrow.

[Phil W]

Remember a similar thing back in 90s on something I was working on. Field in one file padded with spaces, field in other file padded with nulls. Looked the same viewed in text mode. Not so in hex mode. Something staring you in the face when the record matching didn't work...

[FifeingEejit]

Grrrr...

FFS.   

Pad one field with LF.  Pad other with space. Equal in the JOIN? - Are they bollocks. 
Can you see it in grid / via cast to varbinary - No.  Gotta use [sys].[fn_varbintohexstr](...)

Gonna be a severe LART'ing tomorrow.

Doh...

[Chris S]

Ugh. Whitespace. Nuff said.