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;