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;