Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select count(*) and "join" over 3 tables

I am looking for a count query within a join of 3 tables that can get me counts on distinct values of one of these tables.

I have 3 tables that I need to join to get the expected data (Workflow, Message and Message_Workflow_Relation).

I want to get the count of workflows grouped by status + one field of the connected Message table in my result (related_name). The related name should be taken from entries where the adapter field equals wf, however there are sometimes more than one Message record that matches this condition, which will result in more datasets in my count then the ones that are really there.

I am pretty sure it must be possible to sort out the , but just don't get it working. Sadly I cannot change the table structure as it's a given schema of a product we use.

My table structure looks like this:

Workflow:

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 am stuck with this following statement, but I am not sure how to make the selection on the adapter = wf unique for 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_1 instead of 2):

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

How can I make a correct query to achieve this?

Any help appreciated.

like image 450
Sebastian Avatar asked Mar 01 '11 08:03

Sebastian


People also ask

Can we apply join on 3 tables?

In this case the two tables are joined using the relationship table1.id = table2.id . It is possible to use multiple join statements together to join more than one table at the same time. To do that you add a second INNER JOIN statement and a second ON statement to indicate the third table and the second relationship.

Can 3 tables be joined in SQL?

As you can see, joining three tables in SQL isn't as hard as it sounds. In fact, you can join as many tables as you like – the idea behind it is the same as joining only two tables. It's very helpful to take a look at the data midstep and imagine that the tables you've already joined are one table.


1 Answers

You can do this by grouping and counting a distinct 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

This is untested but should work i believe :)

like image 99
F.B. ten Kate Avatar answered Sep 21 '22 22:09

F.B. ten Kate