While I'm really fond of SQL for various things, I don't think your current table structure lends itself to good searching.

I see two potential approaches:

• Restructure your "keywords" table so it contains tuples of (user,keyword). If you have the appropriate indices, you can then search based on either user or keyword and find the "best" matches:

  select
      user, count(*) as hits
  from ...
  where keyword in ('Architect', 'Sydney', 'Small')
  group by user
  order by hits desc
  [download]

• If you want a quick/better solution specialized for search, which already knows about (English) stemming etc., consider looking at ElasticSearch. I don't know how applicable it is for simple keyword search, but at least for documents a little bit larger, it seems to attract a lot of followers.

Two questions:

1. Why do you have "Hong Kong" in the "other" table but "Sydney" in your "string"?
2. Why do you have 2 matches on Record1?

That said, I'm not seeing the Perl question here...

G'day Scotsman,

Welcome to the monastery.

If you've come here to ask a purely SQL question, you're basically in the wrong place. However, assuming you've extracted that data (e.g. via DBI or similar) into Perl data structures, then this might do what you want:

$ perl -Mstrict -Mwarnings -E '
    my @source_data = (
        ["Archeologist", "Ancient projects", "Cairo"],
        ["Architect", "Major projects", "London"],
        ["Architect", "Small projects", "Sydney"],
        ["Architect", "Small", "Hong Kong"],
        ["Programmer", "Big", "Hong Kong"],
    );
    my @search_data = ("Architect", "Hong Kong", "Small");
    my @found_data;

    my $re = "(?:" . join("|", @search_data) . ")";

    for (@source_data) {
        my $matches = 0;
        /$re/ && ++$matches for @$_;
        push @found_data, [$matches, $_] if $matches;
    }

    say "@{$_->[1]}" for sort { $b->[0] <=> $a->[0] } @found_data;
'
Architect Small Hong Kong
Architect Small projects Sydney
Architect Major projects London
Programmer Big Hong Kong
[download]

If you have follow-up questions, that's fine, but please read the "How do I post a question effectively?" guidelines before posting them.