I'm having difficulty coming up with the right join to get my results.
I'm using PostgreSQL 7.2.x
I'm looking for a "most likely match" result. Assume you have a table with two fields, field 1 is a serial key (unique)
andfield 2 is varchar.
Assume that you have the following entries in the table:
recordid val
1, 'a'
2, 'b'
3, 'ab'
And I want to match the strings "a" and "b", but not necessarily "ab", and disregard an additional "c", and organize
theresult so that the records that best match are at the top. Sample output might be:
count recordid
2 3
1 1
1 2
Record #3, containing both "a" and "b" has two count, records 1 and 2 having only one of "a" or "b" have a count of 1.
The closest that I've come so far is from a query like
select id from table where lower(val) like lower('%a%') UNION ALL select id from table where lower(val) like
lower('%b%')UNION ALL select id from table where lower(val) like lower(%c%');
What this gives me is
id
1
3
2
3
which is somewhat close, but then requires me to loop thru a potentially large number of results to get the requested
output.
Anybody else up to this one?
-Ben
