From: bens_nospam@benjamindsmith.com
Message-Id: <200210042105.g94L5BL25197@chico.benjamindsmith.com>
To: <pgsql-sql@postgresql.org>
Subject: Self Join?
Date: Fri, 4 Oct 2002 14:05:11 PDT
Reply-To: <bens_nospam@benjamindsmith.com>
Errors-To: <bens_nospam@benjamindsmith.com>
Content-Type: text/plain;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit

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) and field 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 the result 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