SQL query in many to many relationship (how to)

Posted by Wim on 03-Feb-2017 03:40

Hi,

Im am trying to make a query in a many to many relationschip, but there is no way that i can use the IN word in Rollbase as usual in standard SQL.

Can anyone help?

All Replies

Posted by mpiscoso@gmail.com on 03-Feb-2017 03:45

What do you mean by "there is no way that i can use the IN word in Rollbase as usual in standard SQL." ?

I've been able to use the IN operator in rollbase queries such as rbv_api.selectQuery() and rbf_selectQuery() successfully in the past.

Could you elaborate further?

Posted by Wim on 03-Feb-2017 03:53

I have a n to n relation between a 'shop' and a 'shopgroup'.

So 1 shop can be a related to 0, 1 or more shopgroups.

Is i use SELECT id FROM shop WHERE R_shop_shopgroup = ?, 0, 100, 'shopgroupid' then the result is that i get the id's of the shop that ONLY have that specific shopgroupid. So when the shop ia also related to a second group that shop will not be shown, although the shop is also related to the 'shopgroupid'

Posted by mpiscoso@gmail.com on 03-Feb-2017 03:58

Have you tried:

SELECT id FROM shop WHERE R_shop_shopgroup IN (shopgroupid), 0, 100

Also, I think it would be much easier to use rbv_api.getRelatedIds() or rbv_api.getRelatedIds2() to get shops for your case. It would lessen your processing time as you don't have to perform the query on the whole table but just get the specific data stored on the current record you are on.

Hope this helps

Posted by Wim on 03-Feb-2017 04:08
Hello,
I tried your suggestion an it works well when i use rbv_api.getRelatedIds().
 
Thank you very much!
 
Regards, Wim
 
Van: mpiscoso@gmail.com [mailto:bounce-mpiscosogmailcom@community.progress.com]
Verzonden: vrijdag 3 februari 2017 11:00
Aan: TU.Rollbase@community.progress.com
Onderwerp: RE: [Technical Users - Rollbase] SQL query in many to many relationship (how to)
 
Update from Progress Community
 

Have you tried:

SELECT id FROM shop WHERE R_shop_shopgroup IN (shopgroupid), 0, 100

Also, I think it would be much easier to use rbv_api.getRelatedIds() or rbv_api.getRelatedIds2() to get shops for your case. It would lessen your processing time as you don't have to perform the query on the whole table but just get the specific data stored on the current record you are on.

Hope this helps

View online

 

You received this notification because you subscribed to the forum.  To unsubscribe from only this thread, go here.

Flag this post as spam/abuse.

 
Posted by Wim on 03-Feb-2017 04:18

Hello,

I tried your suggestion and it works well when i use rbv_api.getRelatedIds().

Thank you very much!

Regards, Wim

This thread is closed