JOIN not working as expected

Question

Post reply

JOIN not working as expected

kenneths-1139284

Valued Member

Points: 55
More actions
May 6, 2010 at 2:35 pm

#234671

I used the following query (from segment below) and it returned no results.
FROM T_IDYou.[dbo].[HST] h
LEFT JOIN srv1.T_id.dbo.MO s
ON h.refID=s.DistributorOrd
WHERE s.DistributorOrderNumber is null
However, when I checked the tables side by side, there were 856 records in HST that were not in MO.
How does this happen? Are there bugs in the JOIN reference?
The query above was on separate servers and I was working on the server with the HST table. I thought maybe it was a cross server issue, so I imported the table onto the same server. Still no luck.
Would like to hear if anyone else has seen this issue.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Table definitions? Query looks right, but hard to tell without definitions.

Also... http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kenneths-1139284 Valued Member Points: 55 More actions · Answer 2

I guess that could help with troubleshooting.

h.refID varchar(50)

s.DistributorOrd varchar(50)

WHERE s.DistributorOrderNumber is null should be ...DistributorOrd... still not working

the information is initially imported from the h. table into the s. table. So any records that don't make it should be displayed with the query mentioned earlier.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

kenneths-1139284 (5/6/2010)
I guess that could help with troubleshooting.
h.refID varchar(50)
s.DistributorOrd varchar(50)
WHERE s.DistributorOrderNumber is null should be ...DistributorOrd... still not working
the information is initially imported from the h. table into the s. table. So any records that don't make it should be displayed with the query mentioned earlier.

So, where are the table definitions?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

kenneths-1139284 (5/6/2010)
I guess that could help with troubleshooting.

The link wasn't for troubleshooting, I need the table defs for that. The link was a comment on performance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

While waiting on the table defs, could you try this?

FROM T_IDYou.[dbo].[HST] h

LEFT JOIN srv1.T_id.dbo.MO s

ON h.refID=s.DistributorOrd

AND s.DistributorOrderNumber is null

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning