ok I got it, I think...here is a query using a view and joins on the view and the processinstance table to get the processinstance that is not done with given variables...using the results I can do another query to get the tasks as needed:
select * from jbpm_variableinstance a, (select processinstance_ from jbpm_variableinstance where name_ = 'XYZ' and stringvalue_ = '364834') subView, jbpm_processinstance b where a.processinstance_ = subView.processinstance_ AND a.name_ = 'ABC' AND subView.processinstance_ = b.id_ AND b.end_ IS NULL;
WHERE I am doing a query based on two variables, one called XYZ with a value of 364834 and another variable called ABC. I guess one would have to create more views on the fly to get more data out based on a given variable name and value. Does this look to be the best way?
ok here is my final query. sorry for the multiple posts, but I made some changes at the last second:
select a.* from jbpm_variableinstance a, (select processinstance_ from jbpm_variableinstance where name_ = 'XYZ' and stringvalue_ = '364834') subView, jbpm_processinstance b, jbpm_processdefinition c, jbpm_taskinstance d where a.processinstance_ = subView.processinstance_ AND a.name_ = 'ABC' AND b.id_ = subView.processinstance_ AND b.end_ IS NULL AND c.name_ = 'My Process' AND d.token_ = a.token_ ORDER BY a.stringvalue_;
This final query also joins the process definition table so as to query based on the process definition name. Finally it also joins the token in the variableinstance table to the token in taskinstance table. The end result of all this is a retrieval of all tasks from a given process instance where the process instance contains two variables (one with a given name and value and the other with a given name). The last step is to join on jbpm_taskdefinition to get a certain task by name. Long story short I'll get the tasks and value of the variable. If anyone's interested I'll post the final query.
Any performance tips? Joining all these tables to get out this data in real time is gonna be a headache.