CODEWITHSUNDEEP
sqlpostgresql
gammauniversity
gammauniversity

Reputation: 71

PostgreSQL filter table with LIKE on another table

I have two tables:

Wikpedia:

Id, Title
1,USA
2,USA Army
3,Canada
4,Britain

Events:

Id, Key1, Key2, Key3
1, US, USA, United States
2, Britain, Britain, Brit
3, US, US, US
4, Mex, Mexico, MX

I want to find all entries of titles from Wikipedia table that match either of the three keywords Key1, or Key2 or Key3

Basically, on the frontend side, a user will select a country, according to that I plan to filter the table Events. The keys columns in events table may not necessarily contain country names, it could be something else related to the country, like New York

Thus I have now 3 Keywords, USA, United States, New York, I want to find all relevant titles in Wikipedia from these filtered keywords

Is there are a way to filter data from another table without join?

Query intended [A user input of country and period], however it gives same results for any country in {0} (With help from "a_horse_with_no_name")

select title, count 
from wiki w 
where exists ( select * 
        from eventsxgeog exg 
        where actor1name = '{0}' or actor1geo_fullname = '{0}' 
        or actor2name = '{0}' or actor2geo_fullname= '{0}'
        or actiongeo_fullname= '{0}' and extract(year from dateadded) = {1} 
        and w.title in (exg.actor1name, exg.actor2name, exg.actor1geo_fullname, 
                    exg.actor2geo_fullname, exg.actiongeo_fullname)) 
and year = {1} order by count desc limit 5;

Upvotes: 0

Views: 866

Answers (1)

user330315
user330315

Reputation:

You can use an EXISTS condition:

select wp.*
from wikipedia_table wp
where exists (select * 
              from events e
              where wp.title in (e.key1, e.key2, e.key3));

If you want a partial match, you can use a LIKE condition:

select wp.*
from wikipedia_table wp
where exists (select * 
              from events e
              where wp.title ilike '%'||e.key1||'%'
                 or wp.title ilike '%'||e.key2||'%'
                 or wp.title ilike '%'||e.key3||'%');

Or a bit more compact using ilike any (..):

select wp.*
from wikipedia_table wp
where exists (select * 
              from events e
              where wp.title ilike any (array['%'||e.key1||'%', '%'||e.key2||'%', '%'||e.key3||'%'));

Upvotes: 2

Related Questions