We help IT Professionals succeed at work.

find matches between two tables

fwstealer
fwstealer used Ask the Experts™
on
need to find all rows from drive where the driveid is in the expensereport table

select drives.driveid as 'Apple.drives.driveID', ExpenseReport.driveID as 'Po.ExpenseReport.driveID'
from ExpenseReport with(nolock)
inner join aserver.apple.dbo.drives drive with(nolock) on ExpenseReport.driveID = drive.driveid
where drive.driveid = ExpenseReport.driveID

my error: Error on the 1st line: Conversion failed when converting the varchar value 'No  send me to confirmation page now' to data type int.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
select a.* from expensereport a inner join drives b
on a.driveid=b.driveid

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Wouldn't that still give you the same error??

Commented:
<< Conversion failed when converting the varchar value 'No  send me to confirmation page now' to data type int>>
I don't see a column  'No  send me to confirmation page now' in your query. So I think there is a view involved that isn't correct.

Commented:
This is because the data type of DriveID do not match between Drive table and ExpenseReport table.
Commented:
Also, the last clause :
where drive.driveid = ExpenseReport.driveID
is not needed as you already have ON clause.

The work around for this issue is add a condition to check if the DriveId can be converted to a numeric value.
E.g. if the ExpenseReport.DriveId has string data type, you can change your query becomes :

select drives.driveid as 'Apple.drives.driveID', ExpenseReport.driveID as 'Po.ExpenseReport.driveID'
from ExpenseReport with(nolock)
inner join aserver.apple.dbo.drives drive with(nolock) on ExpenseReport.driveID = drive.driveid
where isnumeric(ExpenseReport.driveID) = 1 

Open in new window