Select a counter (*) and “connect” over 3 tables

Question

I am looking for a query for an account in the union of three tables that can make me count on different values ​​of one of these tables.

I have 3 tables that I need to combine to get the expected data ( Workflow, Messageand Message_Workflow_Relation).

I want to get the number of workflows grouped by status + one field of the connected table Messagein my result ( related_name). The corresponding name should be taken from the records in which the field adapteris equal wf, but sometimes there are several message records that meet this condition, which will lead to more data sets in my account, and then to those that really exist.

I’m sure that it will be necessary to understand, but it just won’t work out. Unfortunately, I cannot change the structure of the table, because it represents a certain scheme of the product we use.

My table structure is as follows:

Procedure:

id | workflow_id | starttime | endtime | status
------------------------------------------------------
1  |          22 |         0 |     200 |     OK
2  |          23 |       220 |     920 |  ERROR
3  |          55 |       202 |     588 |     OK

Message_Workflow_Relation:

id | message_id | workflow_id |
-------------------------------
1  |        122 |          22 |
2  |        235 |          22 |
3  |        456 |          22 |
4  |        982 |          22 |
5  |        444 |          23 |
6  |        445 |          23 |
7  |        585 |          55 |
8  |        738 |          55 |
9  |        399 |          55 |

Message:

id | message_id | starttime | endtime | adapter | related_name |
----------------------------------------------------------------
 1 |        122 |         0 |    2335 |      wf |   workflow_1 |
 2 |        235 |       222 |    1000 |   other |        other |
 3 |        456 |       343 |    2330 | another |      another |
 4 |        982 |       222 |    2200 |      wf |   workflow_1 |
 5 |        444 |      2223 |    3333 |      wf |   workflow_2 |
 6 |        445 |      1123 |    1244 |  manual |       manual |
 7 |        585 |      5555 |    5566 |      wf |   workflow_1 |
 8 |        738 |       655 |     999 |      wf |   worfklow_1 |
 9 |        399 |      6655 |    7732 | another |      another |

This should return the following result:

count(*) | related_name | status |
----------------------------------
       2 |   workflow_1 |     OK |
       1 |   workflow_2 |  ERROR |

I adhere to this following statement, but I'm not sure how to make a choice in adapter = wf uniquefor each workflow:

select distinct
  count(*),
  m.related_name,
  w.status
from
  workflow as w,
  message as m,
  msg_bpm_rel as rel
where rel.workflow_id = w.workflow_id
  and rel.message_id = m.message_id
  and m.adapter = 'PE'
group by m.related_name,w.status

This returns me (4 workflow_1instead of 2):

count(*) | related_name | status |
----------------------------------
       4 |   workflow_1 |     OK |
       1 |   workflow_2 |  ERROR |

How can I make the right request to achieve this?

Any help was appreciated.

Answer 1

You can do this by grouping and counting great value.

So something like:

select count(distinct w.workflow_id), m.related_name,w.status 
from workflow as w, message as m, msg_bpm_rel as rel 
where rel.workflow_id = w.workflow_id and rel.message_id = m.message_id 
and m.adapter = 'PE' 
group by m.related_name, w.status

, , :)

Answer 2

. , . , - :

SELECT distinct theCount
      ,m.related_name
      ,w.status
FROM workflow as w
    ,message as m
    ,msg_bpm_rel as rel
    ,(SELECT count(1) as theCount
            ,w.workflow_id as wf_id
      FROM workflow as w
          ,message as m
          ,msg_bpm_rel as rel 
      WHERE rel.workflow_id = w.workflow_id 
      AND   rel.message_id = m.message_id 
      AND   m.adapter = 'wf' 
      GROUP BY w.workflow_id) AS t

WHERE t.wf_id = w.workflow_id 
AND   rel.workflow_id = w.workflow_id 
AND   rel.message_id = m.message_id 
AND   m.adapter = 'wf'

, . , SELECT- SQL. , , , . , , , .