I have two tables like so CREATE TABLE c (c_no INTEGER PRIMARY KEY, c_name TEXT); CREATE TABLE t (t_no INTEGER PRIMARY KEY, t_name TEXT); CREATE TABLE c_t (c_no INTEGER, t_no INTEGER); Every row in `c` has one or more `t`. I want, in a single SQL, the following
source_c, target_c, similarity where `similarity` is a measure of overlap of `t` between each pair of `c` and is calculated as the number of `t` common between a given pair divided by the total number of unique `t` in the pair. For example, given the following records c t --- --- 1 1 1 2 1 3 2 1 2 5 I want the result to be source_c target_c similarity -------- -------- ---------- 1 2 0.2 (= 1 common `t` between the 2 `c` / by total 4 `t`) Would appreciate any nudge toward a solution. -- Puneet Kishor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users