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