HV

Dear Community,

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   |

 

by Eva Klein
Badge for 'Community Team' achievement
Posted on Mon, 02/14/2022 - 09:39

Hi Harm,

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 :)

RAQL

 

SELECT * 
FROM   S1
LEFT OUTER JOIN S2
  ON (S1.name = S2.name AND S1.location =S2.location)
  WHERE S2.name IS NULL

Kind regards
Eva

0
by Harm Verschuren Author
Posted on Wed, 02/16/2022 - 08:22

Thank you Eva.

0

Featured achievement

Question Solver
Share your expertise and have your answer accepted as best reply.
Recent Unlocks
  • BZ
  • Profile picture for user TEF_Bernd
  • ПЦ
  • CR
  • BH
  • PacMan

Leaderboard

|
icon-arrow-down icon-arrow-cerulean-left icon-arrow-cerulean-right icon-arrow-down icon-arrow-left icon-arrow-right icon-arrow icon-back icon-close icon-comments icon-correct-answer icon-tick icon-download icon-facebook icon-flag icon-google-plus icon-hamburger icon-in icon-info icon-instagram icon-login-true icon-login icon-mail-notification icon-mail icon-mortarboard icon-newsletter icon-notification icon-pinterest icon-plus icon-rss icon-search icon-share icon-shield icon-snapchat icon-star icon-tutorials icon-twitter icon-universities icon-videos icon-views icon-whatsapp icon-xing icon-youtube icon-jobs icon-heart icon-heart2 aris-express bpm-glossary help-intro help-design Process_Mining_Icon help-publishing help-administration help-dashboarding help-archive help-risk icon-knowledge icon-question icon-events icon-message icon-more icon-pencil forum-icon icon-lock