Yeah, one benefit of a long commute is that I could puzzle it out and understand why it works.
select t1.*, ( select v from t2 where t2.k1=t1.k1 and t2.k2=t1.k2 and t2.k3=t1.k3 and t2.k4=t1.k4 and t2.k5=t1.k5 group by k1,k2,k3,k4,k5 having count(*)=1 union select v from t2 where t2.k1=t1.k1 and t2.k2=t1.k2 and t2.k3=t1.k3 and t2.k4=t1.k4 group by k1,k2,k3,k4 having count(*)=1 union select v from t2 where t2.k1=t1.k1 and t2.k2=t1.k2 and t2.k3=t1.k3 group by k1,k2,k3 having count(*)=1 union select v from t2 where t2.k1=t1.k1 and t2.k2=t1.k2 group by k1,k2 having count(*)=1 union select v from t2 where t2.k1=t1.k1 group by k1 having count(*)=1 ) from t1; I haven't fully tested it, but it looks like this does almost what I want. Ideally if something is duplicated in t1, but is unique in t2, this will match the t2 record to both records in t1, and it shouldn't. But I am really close now. David ________________________________ From: Petite Abeille <petite.abei...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Monday, March 11, 2013 5:45 PM Subject: Re: [sqlite] Fuzzy joins On Mar 11, 2013, at 10:32 PM, David Bicking <dbic...@yahoo.com> wrote: > Um, I am wrong, cause I just tried it and sqlite only returns the level 5 > result. I have no clue why! The joins are setup from most specific to broadest. Each join is evaluated only if the previous one returns null (i.e. all these 'and PreviousLevel.Key is null'). _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
