CODEWITHSUNDEEP
sqljoin
unhappymeal
unhappymeal

Reputation: 55

Inner Join results in no records returned even when join criteria exist in both tables

I am trying to perform an Inner Join on two tables and no records are being returned. Both tables are being joined on Postal Code, which is stored as a VARCHAR(50).

    SELECT 
        T1.Name
       ,T1.PostalCode
       ,T2.Address
    FROM FirstTable as T1
        INNER JOIN SecondTable as T2 ON T1.PostalCode = T2.PostalCode

The result is no records are returned. I tried trimming white space from the Postal Code field in both tables and still have no results.

I then tried to narrow down the issue by using a separate select statement. The select statement below returns the record with the Postal Code and Address:

    SELECT PostalCode, Address, FROM SecondTable WHERE PostalCode = 'A1A1A1'

When I try this query on the first table, no records are returned:

    SELECT Name, PostalCode FROM FirstTable WHERE PostalCode = 'A1A1A1'

This is very perplexing because manually viewing the table in Excel reveals a record exists for the postal code.

Does anyone have any thoughts?

Upvotes: 1

Views: 8828

Answers (3)

unhappymeal
unhappymeal

Reputation: 55

I ended up finding the solution using a mixture of all your suggestions. The issue was there was a hidden carriage return character at the end of the postal codes in the first table.

/* See if any postal codes are more than 6 characters long. */
SELECT *, LEN(PostalCode)
FROM FirstTable
WHERE LEN(PostalCode) > 6

/* Find out the ASCII code for the hidden character. */
SELECT ASCII(RIGHT(PostalCode,1))
FROM FirstTable

/* Trimming hidden new line character (ASCII Code 13) from records. */
UPDATE FirstTable
SET PostalCode = REPLACE(PostalCode,CHAR(13),'')

Thanks everyone!

Upvotes: 1

Chris Rodriguez
Chris Rodriguez

Reputation: 918

Its possible you may have hidden newline characters which trim will not remove.

Try running a query against the tables and check the length of the field to see if they match what your expecting.

SELECT Name, PostalCode, LEN(PostalCode)
FROM FirstTable
WHERE LEN(PostalCode) > 6 -- or whatever value.

To remove the newline characters.

UPDATE FirstTable SET PostalCode = SET REPLACE(REPLACE(PostalCode, CHAR(13), ''), CHAR(10), '')

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415780

When I try this query on the first table, no records are returned:

  SELECT Name, PostalCode FROM FirstTable WHERE PostalCode = 'A1A1A1'

Well there you go. Until that works, the join won't work either. So you have some bad data. Here are a few queries you can try to help narrow down what's wrong:

SELECT Name, PostalCode, Len(PostalCode) FROM FirstTable WHERE PostalCode LIKE '%A1A1A1'

SELECT Name, PostalCode, Len(PostalCode) FROM FirstTable WHERE PostalCode LIKE 'A1A1A1%'

SELECT Name, PostalCode, Len(PostalCode) FROM FirstTable WHERE PostalCode LIKE '%A1A1A1%'

SELECT Name, PostalCode, Len(PostalCode) FROM FirstTable WHERE PostalCode LIKE 'A%1A1A1%'

... and more variations on that same theme.

Upvotes: 2

Related Questions