Hi Meng, I'm not sure what exactly you need/want. For set-difference, you need to use SQL EXCEPT, e.g., select ... except select ...; this gives you all rows from the first subquery that are not in the second subquery. For arithmetic difference for each value pair, you'd need to join both tables on a unique join key; e.g., select t1.a1 - t2.a2 from t1 join t2 on t1.id = t2.id; I also noticed that both of your (sub-)queries create a Cartesian product (cross product) of table one_to_many and targets16. Is that indeed intended? Best, Stefan ----- On Jan 20, 2016, at 12:24 PM, integrity 357416268@qq.com wrote:
Dear experts,
I have columns coming from two different datasets which also resides in two different tables in the same layout,now i need the difference of that columns.
Example: sql>select (select mag from one_to_many o, targets16 t where o.new_targetid=t.id) - (select mag from one_to_many o,targets16 t where o.old_uniqueid=t.id) ; cardinality violation (22>1) p.s. these two tables have both 22 rows.
But if I subtract on single values, it works:
select (select avg(mag) from mag1) - (select avg(mag) from mag2);
+--------------------------+
| sql_sub_L1 |
+==========================+
| -0.015908087452235264 |
+--------------------------+
1 tuple (4.486ms)
can any one help me out?
Thanks In Advance !!!!
Meng
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |