J Livingston SQL (4/12/2013)
there appears to be inconsistency in the way you store "item" across the various tables.Not sure if this down to your real tables or just the way you have provided example data etc.
short of time at the moment...but one way to try and get everything together would be as follows...
select *
into #temp
from
(
SELECT V1.vouchertype, V1.voucherprefix, V1.voucherno, I.itemname, V1.batchno, V1.quantity
FROM voucher1 AS V1 INNER JOIN items AS I ON V1.item = I.srno
UNION ALL
SELECT vouchertype, voucherprefix, voucherno, item, batchno, qty
FROM voucherPRETSRET
UNION ALL
SELECT 'CASH MEMO' AS vouchertype, voucherprefix, voucherno, itemname, batch, qty
FROM cashmemo
) x
SELECT #temp.*, voucher.dt
FROM #temp LEFT OUTER JOIN voucher ON #temp.vouchertype = voucher.vouchertype
AND #temp.voucherprefix = voucher.voucherprefix
AND #temp.voucherno = voucher.voucherno
I havent tried this but thank you for your effort and reply
C U Soon and have a nice day