SQL search help needed

Classic Servoy
1

I am trying to get a set of records that fit 2 different sets of criteria (with slightly different relationship ‘chains’ involved in each). Each of the 2 options executes correctly on its own but when put together, separated as shown with ‘OR’, Servoy just hangs and I have to force quit.

var query = "SELECT tasks.tasksid FROM tasks, assets, areas, items"+
" WHERE (tasks.linked_table = 'items'"+
" AND items.area_id = areas.areasid"+
" AND areas.asset_id = assets.assetsid"+
" AND assets.site_id = '"+forms.sites_card.sitesid+"')"+
" OR (tasks.linked_table = 'areas'"+
" AND areas.asset_id = assets.assetsid"+
" AND assets.site_id = '"+forms.sites_card.sitesid+"')"+
" ORDER BY tasks.tasksid";

Can anyone see what the problem here is?

Is the fact that the relationship chains differ in the 2 options a problem?

As an alternative, is it possible to perform more than 1 sql search and add the results of the second search to the first, then load all the relevant records together.

2

Most probably your query is creating a an infinite amount of results becuase of the OR. To join multiple results use the UNION statement.

3

Thanks Jan - I’ll give it a try