How to left join and count two tables where the rows (strings) are different

Question

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; 

Answer 1

@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

Answer 2

Paths as a function of title is perfectly fine. However, using them in a join condition is an anti-pattern. Instead, create a function to generate them.

CREATE FUNCTION path_from_title ( IN title text, OUT path text )
AS $$
  SELECT 'path/' || replace($1, ' ', '-');
$$ LANGUAGE 'sql'
STRICT IMMUTABLE;

Now either join on the natural key (title) which makes things simple, or store a fkey to the title table.

If that doesn't make sense, it's ok to have data as a function of an immutable natural key. Path is f(title). It's weird though to then have to create g(f(title)) as an inverse of f(title) just to do a join.