How to left join and count two tables where the rows (strings) are different PostgreSQL 9.5.6
I have two tables - t1, t2.
t1 has a title column with titles separated by spaces.
t1.title
--------
hello kitty workd
gone wind forever

t2 has a path column with paths separated by dashes and prefixed 'path/'
t2.path
-------
path/hello-kitty-world
path/gone-wind-forever

GOAL: FOR EACH t1.title COUNT THE NUMBER OF TIMES IT SHOWS UP IN t2
Course of action: parse t2.path so that it looks like t1.title in order to do a left join and count**
I'm very new to this, if you have a better approach altogether I would appreciate any suggestions
So far I have:
select t1.title, count(t2.path) as num 
from t1 left join t2 on t1.title = substring(log.path from 6) 
where t1.title like '%'||split_part(substring(log.path from 6),'-',1)||'%' 
group by articles.title;
@ypercube said in question comments:

You don't need to split the strings. If there is always a single space between word, you can replace space with - and then join: ON 'path/' || replace(t1.title, ' ', '-') = t2.path
Or the reverse:  ON t1.title = replace(substring(t2.path, 6), '-', '')

You can use replace() function in the ON clause to compare both strings and get the desired result:


select     title, path
from       t1
inner join t2
on         'path/' || replace(t1.title, ' ', '-') = t2.path;


title             | path                  
:---------------- | :---------------------
gone wind forever | path/gone-wind-forever




select     title, path
from       t1
inner join t2
on         t1.title = replace(substring(t2.path, 6), '-', ' ');


title             | path                  
:---------------- | :---------------------
gone wind forever | path/gone-wind-forever


dbfiddle here