[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Left join issue

Posted on 2009-05-12
6
Medium Priority
?
185 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

650 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