Get Records based on many Conditions in One Column

user_11212517 0
Viewed 93

I have an "ACT_RU_TASK"-table and an "ACT_RU_VARABILES"-table. Every task has many Variables and I want to get a group of tasks based on some Variables for Example:

ACT_RU_TASK

| ID     |  PROC_INST_ID_  |
-----------------------------
|   20      | 35      |

and

ACT_RU_VARABILES

| ID     |  PROC_INST_ID_  |   NAME_  | TEXT_
----------------------------------------------
|   44      | 35      |      initiator | user1
|   45      | 35      |      team      |   team1
|   46      | 35      |      status    |  0
|   47      | 35      |    profile     |  null

Now the Task with PROC_INST_ID_ "35" has many Variables so I wanna get the task or tasks based on Variables Conditions like

I wanna get the task with initiator = "user1" and team = "team1"

I write this Query but I didn't get any Result 

SELECT *
FROM ACT_RU_TASK JOIN
     ACT_RU_VARIABLE
     ON ACT_RU_TASK.PROC_INST_ID_ = ACT_RU_VARIABLE.PROC_INST_ID_
 WHERE (ACT_RU_VARIABLE.NAME_='initiator' AND ACT_RU_VARIABLE.TEXT_='user1') AND   (ACT_RU_VARIABLE.NAME_='team' AND ACT_RU_VARIABLE.TEXT_='team1')

Note: I'm using Activity Framework in Java and I want to create native task Query.

activiti workflow sql java
1 Answers

I think you want aggregation and having for filtering:

SELECT v.PROC_INST_ID_
FROMACT_RU_VARIABLE v
WHERE (v.NAME_ = 'initiator' AND v.TEXT_ = 'user1') OR   
      (v.NAME_ = 'team' AND v.TEXT_ = 'team1')
GROUP BY v.PROC_INST_ID_
HAVING COUNT(*) = 2;   -- both attributes match

This checks that both attributes match. You can join in additional information from other tables. But to get the PROC_INST_ID_ you don't need ACT_RU_TASK.

user_11440350
Related