JOIN by part of two column names

Question

Consider a table with the structure of

id            col1
WqDs.first
WqDs.second
eeRT.first
eeRT.second

How can I JOIN the table to itself using the first part of the id

SELECT idxx, a.col1 AS First, b.col1 AS Second
FROM table1 a JOIN table1 b ON ....

idxx(temporary)    First     Second
WqDs
eeRT

I tried LIKE but it works when an id exists in the id of another row. I thought to trim the first id of the first row and then find it in the second row, but there should be an easier way.

Answer 1

Use locate() to get the position of . and then left() to get the text left of it.

SELECT *
       FROM table1 t11
            INNER JOIN table1 t12
                       ON left(t12.id, locate('.', t12.id) - 1) = left(t11.id, locate('.', t11.id) - 1);