November 17th, 2012, 09:07 PM
How to filter out inverse items when joining a table to itself?
I'm working on a project that's starting to drive me a bit bananas, and I'm hoping someone here can help me to figure out what I'm missing.
I've got a table that contains something like fifteen data fields. I'm trying to get any differing records that match each other by doing a select (select * from table t1, table t2), where the terms are defined as follows:
Two records differ from one another for this purpose if they have different values in field 1. (This was to filter out the cases where a row matched itself between the two copies of the table). They match if any of the following is true: Fields 3-7 match(t1.f3 = t2.f3 AND t1.f4 = t2.f4, etc), fields 9-10 match, or field 11 matches.
I'm getting my data set, but it's got inverse sets in it - that is, if t1.f1 = 'foo' and t2.f1 = 'bar', then I'm getting records for 'foo/bar' and 'bar/foo'. What is the proper way to phrase my sql so that I'm only getting one of the two records in my output?