Solved

Left join issue

Posted on 2009-05-12
6
179 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
  • 3
  • 2
6 Comments
 
LVL 142

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 40

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

770 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