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

postgresql

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;

Best Answer

@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

Related Question