Joining tables with different data types on the common field

[herath]

Is there a way to join 2 tables where the 2 fields on which the tables joined are of different data types(char and varchar)?I tried with CONVERT but is does not give the desired output.

My requirement is to get the minimum (earliest) Posted date along with two other fields.But when i join the two fields I get the duplicate records as well.

Following is my query.


---------------------------------------------------------------------------------------------------------
SELECT DISTINCT RMS_T_CV_SENT_I NFORMATION.ReqR efID,
T.FirstName,MIN (RMS_T_CV_SENT_ INFORMATION.CVP ostedDate) AS Posteddate FROM
RMS_T_CV_SENT_I NFORMATION JOIN

(SELECT DISTINCT RMS_T_CV_INFORM ATION.FirstName ,RMS_T_CV_INFOR MATION.CVRefID
FROM RMS_T_CV_INFORM ATION GROUP BY RMS_T_CV_INFORM ATION.FirstName ,RMS_T_CV_INFOR MATION.CVRefID) AS T
ON CONVERT(varchar (11),SUBSTRING( T.CVRefID,1,11) )=CONVERT(varch ar(11),SUBSTRIN G(RMS_T_CV_SENT _INFORMATION.CV ID,1,11))

GROUP BY RMS_T_CV_SENT_I NFORMATION.ReqR efID,T.FirstNam e

ORDER BY RMS_T_CV_SENT_I NFORMATION.ReqR efID


---------------------------------------------------------------------------------------------------------

In the above ,the RMS_T_CV_SENT_I NFORMATION.CVID and RMS_T_CV_INFORM ATION.CVRefID are the related fields but one is of varchar(15) and other field is of Char(15).
I m sorry ,if this is too lenthy and confusing.

thanks in advance.

[ilearneditonline]

Not knowing really what your data looks like and I am confused why you are joining to recordsets. Can you give some additional details, maybe some comments on why u r doing what u r doing.

[hoomaniraji]

hi
you must create ' views' or 'stored procedure with dataset returning' for each section of your select statement then you can use cast() function for changing the type of your output column in that view. then join that views or storedprocedure s in anaother select statement to output the dataset you need.

[ilearneditonline]

you must create ' views' or 'stored procedure with dataset returning' for each section of your select statement then you can use cast() function for changing the type of your output column in that view. then join that views or storedprocedure s in anaother select statement to output the dataset you need.

Actually, if you are doing it in a stored procedure you could create temp table that holds everything and output that data.

Something along this lines...

 DECLARE @Temp TABLE( 
,CVID varchar(11)
,ReqRefId int
,firstname varchar(20)
,posteddate datetime)
 
INSERT @Temp(CVID, ReqRefId,posteddate)
SELECT DISTINCT CAST(SUBSTRING(CVID,1, 11) AS varchar(11)), ReqRefID, MIN(CVPostedDate)
FROM RMS_T_CV_SENT_INFORMATION
 
UPDATE @Temp
SET firstname=a.FirstName
FROM RMS_T_CV_SENT_INFORMATION a
INNER JOIN @Temp b
ON b.CVID=a.CVRefID
 
SELECT * FROM @Temp

[herath]

Thanks for the replies.I tried creating stored procedures but that doesn't filter the correct records either.

Requirement: Get the minimum posted date ,with the CV holder's name per Requirement Id(ReqRefId)

Fields from RMS_T_CV_SENT_I NFORMATION table :ReqRefID,CVPos tedDate

Fields from RMS_T_CV_INFORM ATION table :FirstName

Common fields are:RMS_T_CV_IN FORMATION.CVRef ID(char 15) and RMS_T_CV_SENT_I NFORMATION.CVID (varchar 15)

---------------------------------------------------------------------------------------------------------------
SELECT DISTINCT RMS_T_CV_SENT_I NFORMATION.ReqR efID,RMS_T_CV_I NFORMATION.Firs tName, MIN(RMS_T_CV_SE NT_INFORMATION. CVPostedDate) AS PostedDate

FROM RMS_T_CV_SENT_I NFORMATION
INNER JOIN RMS_T_CV_INFORM ATION ON
CONVERT(char(15 ),RMS_T_CV_INFO RMATION.CVRefID )=CONVERT(char( 15),RMS_T_CV_SE NT_INFORMATION. CVID)
GROUP BY RMS_T_CV_SENT_I NFORMATION.ReqR efID,RMS_T_CV_I NFORMATION.Firs tName,RMS_T_CV_ SENT_INFORMATIO N.CVPostedDate
ORDER BY RMS_T_CV_SENT_I NFORMATION.ReqR efID

-------------------------------------------------------------------------------------------------------------------

There are 5 other tables too involved in the query with same data mismatch issues.But at least if this part works i could apply the same theory for those as well.The database contains no relationships or primary keys.(I am sorry, it's the worst database created by a fresher).