Join order and unpredictable reults

Question

Post reply

Join order and unpredictable reults

Martin Stephenson

SSCrazy

Points: 2950
More actions
January 15, 2010 at 5:10 am

#214605

I am a little confused about this
when I run the following query
select T.matter_uno , M.CLNT_MATT_CODE,T.BALANCE as bal,T.ACCT_TYPE
from TRM_TRUST T
inner join HBM_MATTER M on t.MATTER_UNO = m.MATTER_UNO
where T.MATTER_UNO= 189270
union all
select M.MATTER_UNO,M.CLNT_MATT_CODE,T.BALANCE as bal ,T.ACCT_TYPE
from HBM_MATTER M
inner join TRM_TRUST T on M.MATTER_UNO = T.TRUST_UNO
where M.MATTER_UNO = 189270--(M.CLNT_MATT_CODE = '300811.00005') --or( M.CLNT_MATT_CODE = '300811.00746' or M.CLNT_MATT_CODE = '300811.00550')
--group by M.CLNT_MATT_CODE,T.TRUST_NUM,T.ACCT_TYPE
union all
select T.MATTER_UNO ,'matter_id',T.BALANCE,T.ACCT_TYPE
from TRM_TRUST T
where T.MATTER_UNO = 189270
I get the following result
matter_uno CLNT_MATT_CODE bal ACCT_TYPE
-------------------------------------------------------------------------------
189270 300811.00005 8.2300 A
189270 300811.00005 0.0000 A
189270 matter_id 8.2300 A
there is a 1 to 1 relationship between the two tables and there is a balance of 8.23 in the trust table so why does it come back as zero when I join trust table to matter table and 8.23 when I join matter table to trust table
I've attached XML queryplan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

What do you get from ..

select balance from TRM_TRUST where MATTER_UNO= 189270

select balance from HBM_MATTER where MATTER_UNO= 189270

Clear Sky SQL
My Blog[/url]

Martin Stephenson SSCrazy Points: 2950 More actions · Answer 2

balance isn't in the Matter table, but as there is one trust for table for the matter table joined on the matter_uno field, the result should be the same every time ?

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

My bad.

On the face of it i cant see any reason.

What app was the queryplan from ?

Can you send a standard one from SSMS ?

Oh and full DDL for the tables pleas

Clear Sky SQL
My Blog[/url]

Martin Stephenson SSCrazy Points: 2950 More actions · Answer 4

Martin Stephenson

SSCrazy

Points: 2950

January 15, 2010 at 6:39 am

#1104159

Apex SQL edit , but here is one from SSMS

and the ddl

Martin Stephenson SSCrazy Points: 2950 More actions · Answer 5

proof reading is everything

if you dont match keys you get odd results

I only spoted it after going and doing something else for half an hour and then returning to it.

Problem I have now is that the vendor supplied application appears to be using the wrong key match as well