No data returned in JOINED columns

Question

Post reply

No data returned in JOINED columns

duanecwilson

Ten Centuries

Points: 1057
More actions
June 18, 2010 at 9:16 am

#223983

This join returns no data in the last two columns. I am looking at, in particular, empid on second line.
As you can see, WB_LOGON_ID is in WB_WF_EMPLID_XREF AND #t. What am I missing?
The second line of table #t should have A767108 in WB_LOGON_ID in results and 00001836553 in X_EMPLID
What am I missing here?In the real query there are 2300 rows and NO data in the last 2 columns
<pre lang="x-sql">select DISTINCT #t.*, X.EMPLID X_EMPLID, X.WB_LOGON_ID from #t
left join WB_WF_EMPLID_XREF X on X.WB_LOGON_ID = #t.empid
Results:
lastnamefirstnameempidEMPLIDEMAIL_IDX_EMPLIDWB_LOGON_ID
SmithThomas0000142723200001427232Tom.J.Smith@randomemail.comNULLNULL
McWilliamsTimothyA767108NULLNULLNULLNULL
WaltonDonaldA819301NULLNULLNULLNULL
Table WB_WF_EMPLID_XREF:
EMPLIDWB_LOGON_IDWB_EMPLID
00001836553A767108992947
As you can see, even though only one record is shown, the data IS in WB_WF_EMPLID_XREF. I did check the length of
both columns and they were 7, so there were no spaces.

Viewing 4 posts - 1 through 3 (of 3 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

What do you get when you run this?

DECLARE @CommonColumn VARCHAR(20)

SET @CommonColumn = 'A767108'

SELECT * FROM #t WHERE empid = @CommonColumn

SELECT * FROM WB_WF_EMPLID_XREF X WHERE X.WB_LOGON_ID = @CommonColumn

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

duanecwilson (6/18/2010)
...
What am I missing here?
...

It's simple! You are missing the setup of tables and sample data population scripts to represent your question/case in a helpfull way, which would allow someone here to spend minimum time and help you best.

Please read the following:

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

duanecwilson Ten Centuries Points: 1057 More actions · Answer 3

Thank you Eugene for the forum posting tips. Ironically, I just found, read them, and printed them about 10 minutes ago.

And Chris, I actually found that I had 2 different panes open in SSMS, one for a local server, and one for a remote server. I had an empty table on localhost and a populated one on the remote host. When I saw the data, I was looking at the remote. When I ran the query, I was running on localhost. So all I had to do is use a linked server which I had already set up. I will try your code on Monday, as it looks like an interesting alternative anyway.

Anyway, thanks both of you for your input.