[SOLVED] How to indirectly join two tables

How to indirectly join two tables

I have two tables

perid  firstname lastname 
1       mike      prokosini
2       nick      doug
3       paul      jor
4       riki      thui
5       mun       tyui

and second table

caseno   name 
13       mike-prokosini
32       nickdoug
33       paul l.jor
34       riki  thui
35       mun kl. tyui

I must join these tables such that if firstname is a substring of name AND lastname is a substring of name THEN the rows should be connected.

I know this is not correct, but I cannot even imagine how we can possibly do this

  SELECT * 
    FROM table1 , table2 
    WHERE firstname LIKE '%name%' AND lastname LIKE '%name%'

Please help me

Solution

You need to concatenate the actual column values with the wildcard:

SELECT * 
FROM table1 t1
  JOIN table2 t2 ON t2.name LIKE '%'||t1.firstname||'%' 
                AND t2.name LIKE '%'||t1.lastname||'%'