Solved

Left join issue

Posted on 2009-05-12
6
182 Views
Last Modified: 2013-11-05
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

0
Comment
Question by:scross1276
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24365168
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
 

Author Comment

by:scross1276
ID: 24365220
The data types are nvarchar and char.  That would lead me to believe the collations are different, unicode versus non-unicode.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24365260
>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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:scross1276
ID: 24365361
No luck guy, I still have the same number of rows as when I don't cast...
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24365537
What is the output of your query and what is your expected result. give an example.
0
 

Author Comment

by:scross1276
ID: 24365724
Ugh, nevermind, I think I just found it.  Thanks for your help!!  Points are yours.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

726 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