Mysql – JOIN by part of two column names

join;MySQLqueryself-join

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.

Best Answer

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

Related Solutions

Sql-server – Forcing Join Order

It seems that I can specify OPTION (FORCE ORDER) at the end of the query and that will make the joins happen in the right order. There are lots of people warning not to do this because it stops SQL from optimising my query so I deffinately will be using it sparingly (and watching those queries closely).

SQLite – How to Perform a Join Between Two Tables

I assume when you say JOIN you really mean a more general combination of the two tables. JOIN is an operator in SQL, so you should avoid using it when you are referring to a UNION. Anyhow, if I get it right you want a FULL OUTER JOIN between two tables:

select COALESCE(a.filename, b.filename) as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1
from T1 as a
FULL JOIN T2 as b
    ON a.filename = b.filename

COALESCE is a function that return the first value from left to right that is not null.

However, from what I understand SQLite only supports LEFT JOIN so you have to rewrite the query using two LEFT JOINS and a UNION as:

select a.filename as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1 
     , CASE WHEN b.filename IS NOT NULL -- a match
            THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)  
                      THEN 0 ELSE 1
                 END
       END     
from T1 as a
LEFT JOIN T2 as b
    ON a.filename = b.filename
UNION 
select b.filename as filename
     , a.filesize as filesize1, a.permissions as permissions1
     , b.filesize as filesize1, b.permissions as permissions1 
     , CASE WHEN a.filename IS NOT NULL -- a match
            THEN CASE WHEN (a.filesize, a.permissions) = (b.filesize, b.permissions)  
                      THEN 0 ELSE 1
                 END
       END     
from T2 as b
LEFT JOIN T1 as a
    ON a.filename = b.filename

Note that a and b changed place in the second leg of the union.

Best Answer

Related Solutions

Sql-server – Forcing Join Order

SQLite – How to Perform a Join Between Two Tables

Related Question