Multiple records in SQL

Question

UPDATE first i try this sp with filter id

select Row_number()
 OVER(ORDER BY (SELECT 1))  AS 'RowNumber',
 vf.* 
 from login ld,
 veh vf  where
(ld.fid=vf.fid) 
and ld.id = '0257'
and ld.id <> 'NA' 
and vf.CustID <> 'None'  
and vf.Simo <> 'None'  
Order by Row_number() OVER(ORDER BY (SELECT 1))

and this show result like this

RowNumber   FID     Oid     CustID      DID     Simo    RNo
1          166  MT255   M2522      1368     LM00    LE270

now when i remove filter ld.id and try this

select Row_number() OVER(ORDER BY (SELECT 1))  AS 'RowNumber',
 vf.* 
 from login ld,
 veh vf  where
(ld.fid = cast(vf.fid as varchar(max)))
and ld.id <> 'NA' 
and vf.CustID <> 'None' 
and vf.SimNo <> 'None'  
Order by Row_number() OVER(ORDER BY (SELECT 1))

then this show mutlple result

RowNumber   FID     Oid     CustID      DID     Simo    RNo
1          166    MT255   M2522      1368     LM00    LE270
2          166    MT255   M2522      1368     LM00    LE270
3          166    MT255   M2522      1368     LM00    LE270

and when i remove this

(ld.fid = cast(vf.fid as varchar(max)))

and write this

(ld.fid=vf.fid) 

then this show error Conversion failed when converting the varchar value 'none' to data type int.

update

when i select * from Login where iD='0257' then

ID    FID   
0257    166 

and when i try select * from Login where FID='166' then

ID          FID 
0257        166 
022         166 
0258        166

and select * from Vehicle where FID='166'

FID     Oid     CustID      DID     Simo    RNo
 166  MT255   M2522      1368     LM00    LE270

Answer 1

to fix the conversion error you can try this
(Assuming login.fid is a varchar field and veh is not a varchar field)

SELECT  distinct 
        vf.RegNo
FROM login ld
  inner join veh vf on ld.fid = convert(varchar, isnull(vf.fid,''))
where ld.id  <> 'NA'
AND vf.CustID  <> 'None'
AND vf.Simo  <> 'None'

EDIT: ok what if login.fid is varhcar and vhe,fid is int then how to convert login.fid to int?

SELECT  distinct 
        vf.RegNo
FROM login ld
  inner join veh vf on convert(int, isnull(ld.fid, 0)) = vf.fid
where ld.id  <> 'NA'
AND vf.CustID  <> 'None'
AND vf.Simo  <> 'None'

This will go wrong if there is any value in login.fid that is not convertable to int

EDIT: you can try to fix that like this but there is no guarantie it will always work.

SELECT  distinct 
        vf.RegNo
FROM login ld
  inner join veh vf on case when isnumeric(ld.fid) = 1 then convert(int, isnull(ld.fid, 0)) else -1 end = vf.fid
where ld.id  <> 'NA'
AND vf.CustID  <> 'None'
AND vf.Simo  <> 'None'