2

I am not sure why the below join query is not returning any results.

SELECT * FROM table1 JOIN table2  ON table1.fieldid = table2.idfield 

The below 2 queries return results so I am not sure why the join wont pick up any rows when i can clearly see that both tables have join field values in common.

SELECT *  FROM table1 WHERE fieldid =  900399530  (returns rows)
SELECT *  FROM table2 WHERE idfield =  900399530  (returns rows)

Any ideas?

Below are the data types for the 2 columns:

fieldid [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

idfield [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

  • 4
    what are the datatype of both columns? – Lamak Jan 22 '18 at 15:41
  • Can you show us a demo of your table definition and sample data where this fails? – Mark Sinkinson Jan 22 '18 at 15:43
  • 1
    Try it by using single quotes: '900399530'. Maybe there is some extra space in any of your tables. – McNets Jan 22 '18 at 16:35
8

As far as it's a CHAR/VARCHAR/NVARCHAR field you should use single quotes:

Maybe there is some extra or leading space.

Try it on this way:

SELECT *  FROM table1 WHERE fieldid =  '900399530';
SELECT *  FROM table2 WHERE idfield =  '900399530';

or remove additional spaces by using TRIM() function.

SELECT * FROM table1 JOIN table2  ON TRIM(table1.fieldid) = TRIM(table2.idfield);

For versions below 2017:

SELECT *
FROM   table1
JOIN   table2
ON     LTRIM(RTRIM(table1.fieldid)) = LTRIM(RTRIM(table2.idfield));
| improve this answer | |
  • looks like you are right. when i add the single quotes , table 2 no longer returns any records. I will trim the fields in both tables as it does appear there are some spaces in there. if the join works after that ,i'll come back and accept your answer. Thanks!! – Juan Velez Jan 22 '18 at 16:46
  • 1
    Standard SQL ignores trailing spaces for comparison but leading spaces are significant. – Michael Green Jan 24 '18 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.