Compare query where ID's are different

Hi,

I need help with this query. It is to compare pre conversion data to post conversion data. recno = roid + 2000 (meaning that we added 2000 to the roid so the id's would not hit existing records in the target table.

Here is what I have so far:

SELECT
	`tpsmotorsports source`.`orders money`.recno AS recno,
	`tpsmotorsports target`.ledger.roid,        -- is recno + 2000
	`tpsmotorsports source`.`orders money`.taxamt,
	`tpsmotorsports target`.ledger.amount,
	`tpsmotorsports target`.ledger.transactiontype
FROM
	`tpsmotorsports source`.`orders money`
LEFT JOIN `tpsmotorsports target`.ledger ON `tpsmotorsports source`.`orders money`.recno = `tpsmotorsports target`.ledger.roid
WHERE
	transactiontype = 'RO - Taxes Owed'

Select allOpen in new window

For this to work I need to have the join factor in the 2000 increase in the roid value. For example recno 1 will be roid 2001. I am not clear on how to do this and what I have tried has failed. Any idea how to make this work?

Thanks,
c