Left join issue

I need to write a query that returns all rows from tableA that do not exist in tableB where tableB is in another database.  I can't use a simple sub-select so I have to use a left join, I think...  The problem is that the query is returning to many rows so I must not have it right.
select *
from tableA a
	left join server.db.owner.tableB a1 on a.number = a1.number
where a1.number is null

Open in new window

scross1276Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>The data types are nvarchar and char.
the problem is that CHAR is right-padded with spaces.
so, for the join, you have to cast to nvarchar.

example:
select *
from tableA a
	left join server.db.owner.tableB a1 on a.number = cast(a1.number as nvarchar(20))
where a1.number is null

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the query is correct syntax.
so, if you get too many records, then you have a data type issue, probably.
what is the data type (and collation) of the fields "number" in the 2 tables.
0
 
scross1276Author Commented:
The data types are nvarchar and char.  That would lead me to believe the collations are different, unicode versus non-unicode.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
scross1276Author Commented:
No luck guy, I still have the same number of rows as when I don't cast...
0
 
SharathData EngineerCommented:
What is the output of your query and what is your expected result. give an example.
0
 
scross1276Author Commented:
Ugh, nevermind, I think I just found it.  Thanks for your help!!  Points are yours.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.