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_INFORMATION.ReqRefID,
T.FirstName,MIN(RMS_T_CV_SENT_INFORMATION.CVPosted Date) AS Posteddate FROM
RMS_T_CV_SENT_INFORMATION JOIN

(SELECT DISTINCT RMS_T_CV_INFORMATION.FirstName,RMS_T_CV_INFORMATIO N.CVRefID
FROM RMS_T_CV_INFORMATION GROUP BY RMS_T_CV_INFORMATION.FirstName,RMS_T_CV_INFORMATIO N.CVRefID)AS T
ON CONVERT(varchar(11),SUBSTRING(T.CVRefID,1,11))=CON VERT(varchar(11),SUBSTRING(RMS_T_CV_SENT_INFORMATI ON.CVID,1,11))

GROUP BY RMS_T_CV_SENT_INFORMATION.ReqRefID,T.FirstName

ORDER BY RMS_T_CV_SENT_INFORMATION.ReqRefID


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

In the above ,the RMS_T_CV_SENT_INFORMATION.CVID and RMS_T_CV_INFORMATION.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 storedprocedures 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 storedprocedures 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_INFORMATION table :ReqRefID,CVPostedDate

Fields from RMS_T_CV_INFORMATION table :FirstName

Common fields are:RMS_T_CV_INFORMATION.CVRefID(char 15) and RMS_T_CV_SENT_INFORMATION.CVID(varchar 15)

---------------------------------------------------------------------------------------------------------------
SELECT DISTINCT RMS_T_CV_SENT_INFORMATION.ReqRefID,RMS_T_CV_INFORM ATION.FirstName, MIN(RMS_T_CV_SENT_INFORMATION.CVPostedDate) AS PostedDate

FROM RMS_T_CV_SENT_INFORMATION
INNER JOIN RMS_T_CV_INFORMATION ON
CONVERT(char(15),RMS_T_CV_INFORMATION.CVRefID)=CON VERT(char(15),RMS_T_CV_SENT_INFORMATION.CVID)
GROUP BY RMS_T_CV_SENT_INFORMATION.ReqRefID,RMS_T_CV_INFORM ATION.FirstName,RMS_T_CV_SENT_INFORMATION.CVPosted Date
ORDER BY RMS_T_CV_SENT_INFORMATION.ReqRefID

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

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).