I am struggling with RAQL trying to figure out a workaround or alternative for the EXISTS statement. If I am correct, the EXISTS statement is not supported. How do I solve this problem below in Mashzone? (is it even possible w/o the exists statement?). Any help highly appreciated. TIA.
Cheers, Harm
My use case: I have 2 data sets, DS1 and DS2, and want to query the records that are in DS1 but filtering out the records that are also in DS2, based on some attribute values. Hereafter simplified data sets to illustrate the use case. In 'regular' SQL the approach would be to use the exists as shown below (verified using mariadb database).
DS1
name |sport |location |
-----+----------+---------+
john |tennis |utrecht |
elton|football |london |
clara|chess |stockholm|
john |football |stockholm|
truus|volleyball|london |
DS2
name |location |
-----+---------+
elton|london |
john |stockholm|
piet |utrecht |
Required output:
select *
from DS1
where not exists
(select 1 from DS2
where DS1.name = DS2.name
and DS1.location = DS2.location
);
name |sport |location |
-----+----------+---------+
john |tennis |utrecht |
clara|chess |stockholm|
truus|volleyball|london |
If I have understood the problem completely correctly, this might help you. However, I only tested it really fast and it still needs some optimization :)
SELECT *
FROM S1
LEFT OUTER JOIN S2
ON (S1.name = S2.name AND S1.location =S2.location)
WHERE S2.name IS NULL
