Odd Join Behavior

Posted on 2011-10-26
Last Modified: 2012-05-12
Unfortunately I cannot give you a sample, because a sample works.

Basically I have two tables that each contains several fields.  The tables were created by a third party application, and the data in them was inserted by that same app (basically no accidental mistyping of data).

The first table, we'll call Invoice, the second we'll call JobMaster

My query looks like this:
    LEFT OUTER JOIN dbo.JobMaster
        ON dbo.JobMaster.Company = dbo.Invoice.Company
        AND dbo.JobMaster.JobNo = dbo.Invoice.JobNo

Open in new window

For some reason, nothing from JobMaster is coming through (it's all NULL)

I know a record exists and can see the JobNo values in both tables.  I can copy the JobNo value returned from the query and put it in the WHERE clause of a separate query on JobMaster and the record will be returned.  I even tried LTRIM(RTRIM()) just in case there were some spurious spaces not apparent but that didn't work either.

Now I'll be the first to admit, I'm not a DBA or even a full fledged DB Developer, but this is a simple query of the type I've done dozens of times in the past.

What's up?
Question by:Clif
    LVL 9

    Accepted Solution

    Is the JobNo for the same company?  You have the join based on Company AND JobNo.

    LVL 11

    Author Comment

    Yes, I made sure of this as well.
    LVL 11

    Author Closing Comment

    I did, but did not try trimming them.  Once I did, the query worked.

    Thanks for leading me in the right direction.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now