In article <C07F9BFD-5FC1-4B8B-BA87-C8BDC47D0136@solfertje.student.utwente.nl>,
Alban Hertroys <dalroi@solfertje.student.utwente.nl> 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