In article <C07F9BFD-5FC1-4B8B-BA87-C8BDC47D0136@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>, Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> writes: > An example of the two sets I need to "join" are, at the left hand side: > unit | token | exponent > -------+-------+---------- > m.s^-1 | m | 1 > m.s^-1 | s | -1 > m.s^-2 | m | 1 > m.s^-2 | s | -2 > And at the right hand side: > token | exponent > -------+---------- > m | 1 > s | -2 > The goal of the query is to find which unit at the left hand side > matches all the tokens and exponents at the right hand side, which > would be 'm.s^-2' in the above example. The order in which the tokens > are returned can be random, there isn't really a defined order as it > doesn't change the meaning of a unit. > I do have a possible solution using array_accum [1][2] on an ordered > version (on unit,token,exponent) of these sets. It's not a pretty > solution though, I'm not happy with it - it's a transformation (from a > set to an array) where I feel none should be necessary. Isn't there a > better solution? Hm, how about a "double negation", i.e. return all units except those with a non-match? In SQL: SELECT t1.unit FROM t1 EXCEPT SELECT t1.unit FROM t1 LEFT JOIN t2 ON t2.token = t1.token AND t2.exponent = t1.exponent WHERE t2.token IS NULL -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
