0

I have three tables with data for which I want to get a combined overview created using an SQL query. The first table "Epics" conists of a column with key values and another column which contains a list of keys. The second and third tables contain a status for each of these keys.

The tables can be illustrated like follows:

Epics
  Key      Sub-Tasks
+--------+----------------+
| MCR-1  | MCR-21, MCR-31 |
+--------+----------------+
| MCR-2  | MCR-22, MCR-32 |
+--------+----------------+
| MCR-3  | MCR-23, MCR-33 |
+--------+----------------+

QM Sub-Tasks
  Key      Status
+--------+-------------+
| MCR-21 | DONE        |
+--------+-------------+
| MCR-22 | OPEN        |
+--------+-------------+
| MCR-23 | IN PROGRESS |
+--------+-------------+


E3 Sub-Tasks
  Key      Status
+--------+--------------+
| MCR-31 | NOT RELEVANT |
+--------+--------------+
| MCR-32 | DONE         |
+--------+--------------+
| MCR-33 | OPEN         |
+--------+--------------+

Now I created the following SQL statement:

SELECT epics.'Key' AS 'MCR-Key'
    qm.'Status' AS 'QM Status'
    e3.'Status' As 'E3 Status'
FROM T3 epics
    LEFT JOIN T1 qm ON (qm.'Key' IN epics.'Sub-Tasks')
    LEFT JOIN T2 e3 ON (e3.'Key' IN epics.'Sub-Tasks')

However, by using this statement, only the first row contains the status values of tables two and three, but all subsequent rows only contain the epic's key:

Output
  MCR-Key   QM Status      E3 Status
+---------+--------------+-----------+
| MCR-1   | NOT RELEVANT | DONE      |
+---------+--------------+-----------+
| MCR-2   |              |           |
+---------+--------------+-----------+
| MCR-3   |              |           |
+---------+--------------+-----------+

Any idea why this is the case and how I can resolve this issue?

taocp
  • 123
  • 3
  • Please tag RDBMS that you use. – Marko Ivkovic Jun 23 '21 at 11:25
  • I must admit that I do not know what is used under the hood. I suppose MySQL or PostgreSQL. I am just using a macro plugin (called Table Transformer) for Confluence where the results from different filters (-> tables) can be aggregated and transformed into user-defined views. – taocp Jun 23 '21 at 11:55
  • Since you don't know which RDBMS you are using, logic is in the answer. Just adapt to you. – Marko Ivkovic Jun 23 '21 at 12:07

1 Answers1

0

I must 1st split string from epics.sub-tasks and left part to join with QM sub-tasks table, right to join with E3 sub-tasks table.

The answer will be:

SELECT 
     e.[Key] 'MCR-Key',
     q.[Status] 'QM Status',
     e3.[status] 'E3 Status'
FROM Epics e
LEFT JOIN [QM Sub-tasks] Q 
       ON Q.[Key] = SUBSTRING(e.[sub-tasks], 0, CHARINDEX(',', e.[sub-tasks]))
LEFT JOIN [E3 Sub-tasks] E3
       ON E3.[Key] = right(e.[sub-tasks], charindex(',', reverse(e.[sub-tasks])) - 2)

Here you can check my demo on DB<>FIDDLE


And .... I must say something more

NEVER NAME YOUR COLUMN WITH RESERVED WORDS

and do NOT use hyphen (-) into column name

Marko Ivkovic
  • 1,262
  • 1
  • 11
  • 14
  • Thanks a lot for your help. It seems like the system is definitely not MSSQL ;-) Isn't there a more generic solution for the JOINs? I am asking, since the number of sub-tasks can be also more than two for a given epic. I just wanted to keep the example simple. – taocp Jun 23 '21 at 12:36
  • In any way, you must split string from sub-tasks from epics and join to how many tables you have. If you are using MySQL, you can achieve with `substring_index()` check this [question](https://stackoverflow.com/questions/28431268/mysql-substring-between-two-strings/28432157). With this, you will get as many columns as you have strings separated by commas. – Marko Ivkovic Jun 23 '21 at 12:48