CODEWITHSUNDEEP
mysqlcasting
Aina
Aina

Reputation: 653

Joining two tables by two columns where the data types don't match

I am trying to join two tables of a MySQL database one two columns:

SELECT ol.utdate, ol.session, dl.utdate, dl.dsession FROM observation_log ol 
LEFT OUTER JOIN diary_log dl ON (ol.utdate = dl.utdate AND ol.session = dl.dsession);

The trouble is ol.session is a CHAR and dl.dsession is a TINYINT(4). The correspondence between the two is integers for letters in the alphabet:

=======================
ol.session||dl.dsession
=======================
A         ||1
B         ||2
C         ||3
...
=======================

How can I convert (map or cast, not sure the correct verb to use here) either ol.session or dl.dsession to either both be a letter or an integer?

Thanks, Aina.

Upvotes: 2

Views: 52

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

You can use the MySQL ASCII() function to convert upper case letters from the ol.session column to numbers which should match the dl.session column:

SELECT
    ol.utdate,
    ol.session,
    dl.utdate,
    dl.dsession
FROM observation_log ol 
LEFT OUTER JOIN diary_log dl
    ON ol.utdate = ol.utdate AND
       ASCII(ol.session) - 64 = dl.dsession

Note that ASCII('A') returns 65, with ASCII('B') returning 66, and so on. So if we subtract 64 from ASCII(ol.session) we can bring things into alignment.

Upvotes: 4

Related Questions