Solved

Left join issue

Posted on 2009-05-12
6
177 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now