By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,223 Members | 1,434 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,223 IT Pros & Developers. It's quick & easy.

Joining tables with different data types on the common field

P: 2
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.
Jul 25 '07 #1
Share this Question
Share on Google+
4 Replies


ilearneditonline
Expert 100+
P: 130
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.
Jul 25 '07 #2

P: 10
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.
Jul 25 '07 #3

ilearneditonline
Expert 100+
P: 130
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...

Expand|Select|Wrap|Line Numbers
  1.  DECLARE @Temp TABLE( 
  2. ,CVID varchar(11)
  3. ,ReqRefId int
  4. ,firstname varchar(20)
  5. ,posteddate datetime)
  6.  
  7. INSERT @Temp(CVID, ReqRefId,posteddate)
  8. SELECT DISTINCT CAST(SUBSTRING(CVID,1, 11) AS varchar(11)), ReqRefID, MIN(CVPostedDate)
  9. FROM RMS_T_CV_SENT_INFORMATION
  10.  
  11. UPDATE @Temp
  12. SET firstname=a.FirstName
  13. FROM RMS_T_CV_SENT_INFORMATION a
  14. INNER JOIN @Temp b
  15. ON b.CVID=a.CVRefID
  16.  
  17. SELECT * FROM @Temp
  18.  
Jul 25 '07 #4

P: 2
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).
Jul 26 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.