SQL: is this query possible?

[Hurra]

Hello!
 I model as the following image:



In my documentation I "start" from Stereotype 1 Element, hence #OBJECTID# is the Object_ID of Stereotype 1 Element.

What I want is a table like the following:

Stereotype 2 Element 1 |	-
Stereotype 2 Element 2 |	Stereotype 3 Element 1

In text;
The first column should list all related Stereotype 2, and if there is a Stereotype 3 related to both the Stereotype 2 and the "start" (Stereotype 1), Stereotype 3 should be in the second column.

Getting column one is easy;

Code: [Select]

select distinct stereo1.ea_guid AS CLASSGUID, stereo1.Object_Type AS CLASSTYPE, stereo1.Name AS stereo1Name
from ((t_object stereo1
inner join t_connector start2stereo1Connector on start2stereo1Connector.End_Object_ID = stereo1.Object_ID AND stereo1.Stereotype = 'Stereotype 1')
inner join t_object start on start2stereo1Connector.Start_Object_ID = #OBJECTID#)

But how do I add the second column? I have tried a lot of stuff but not quite got there.


I would prefer an SQL-solution if possible and avoiding scripting.

[qwerty]

I guess you'd better be served when asking on StackOverflow with a SQL tag.

q.

[Hurra]

I guess you'd better be served when asking on StackOverflow with a SQL tag.

q.

Roger that!

[Hurra]

I think I'm almost there. Perhaps someone can see what's missing:

Code: [Select]

select stereo2.ea_guid AS CLASSGUID, stereo2.Object_Type AS CLASSTYPE, stereo2.Name, stereo3.Name, stereo3.Note as stereo3Note
from ((((t_object stereo2
INNER JOIN t_connector stereo1Con on stereo1Con.Start_Object_ID = #OBJECTID)
INNER JOIN t_connector stereo3ConOut on stereo3ConOut.End_Object_ID = stereo1Con.End_Object_ID)
INNER JOIN t_object stereo3 on stereo3.Object_ID = stereo3ConOut.Start_Object_ID)
INNER JOIN t_connector stereo3ConIn on stereo3ConIn.Start_Object_ID = #OBJECTID# AND stereo3ConIn.End_Object_ID = stereo3.Object_ID)
WHERE stereo2.Object_ID = stereo1Con.End_Object_ID AND stereo2.Stereotype = 'Stereotype 2'

Output:
Column 1 lists Sterotype 2
Column 2 (and 3) lists correct Stereotype 3

However, if Stereotype 2 has no related Stereotype 3, like Stereotype 2 Element 1 in my example figure, it will not be included in the list.

Which I would like to have, and the entry in column 2 would then be blank or '', NA, null or similar.

Someone can help with the last piece of the puzzle? 

[Hurra]

I will do one last try to explain my problem.

We have modelled a system structured with parts we call modules, i.e. communication types, Voice, Chat, VTC etc..

Each module has a lot of requirements related to them.

Each requirement has 0..n answers related to them, and this answer can be related to the module in focus.

The small example below we look at an VoIP, which has three requirements, and two answers.



The desired output is a table with ALL related requirements, and also the answers, if there are an answer. For this example the output should be:

SFBK_AUT.1 --- Tal Auth av subjekt 1.1
SFBK_UID.1 ---
SFBK_UID.2 --- Tal Unikt ID 1.1



The reason we model this way is to be able to reuse these answers/interpretations, in other words, several modules can have the same answer for different requirements.


I think a lot of you can help me with this, I've just been bad att explaining what the problem actually is.

The SQL-query in my previous post yields the following table:

SFBK_AUT.1 --- Tal Auth av subjekt 1.1
SFBK_UID.2 --- Tal Unikt ID 1.1

A simple LEFT JOIN does not give me the table I'm looking for.

I would appreciate all help I can get!
Thank you!

[Geert Bellekens]

You have to left join both the join to the connector to the answer as the join to the to object answer.

The problem with that is that you often get duplicate lines because there are connectors that match without matching elements.

The solution is to join with a subquery.

Something like this:

Code: [Select]

select *
from t_object module
inner join t_connector mrq on mrq.Start_Object_ID = module.Object_ID
inner join t_object rq on rq.Object_ID = mrq.End_Object_ID and mrq.Stereotype = 'reqStereo' -- inner join with requirement
left join (select rqa.Start_Object_ID, answer.Name as Answer from t_connector rqa
   inner join t_object answer on answer.Object_ID = rqa.End_Object_ID
   where answer.Stereotype = 'answerStereo')  ans on ans.Start_Object_ID = rq.Object_ID -- left join with answer
Geert

[Hurra]

You have to left join both the join to the connector to the answer as the join to the to object answer.

The problem with that is that you often get duplicate lines because there are connectors that match without matching elements.

The solution is to join with a subquery.

Something like this:

Code: [Select]

select *
from t_object module
inner join t_connector mrq on mrq.Start_Object_ID = module.Object_ID
inner join t_object rq on rq.Object_ID = mrq.End_Object_ID and mrq.Stereotype = 'reqStereo' -- inner join with requirement
left join (select rqa.Start_Object_ID, answer.Name as Answer from t_connector rqa
   inner join t_object answer on answer.Object_ID = rqa.End_Object_ID
   where answer.Stereotype = 'answerStereo')  ans on ans.Start_Object_ID = rq.Object_ID -- left join with answer
Geert

Thank you so much for your time Geert, I really appreciate it!

I can't quite get my head around the join with subquery. I played around a bit but never got the result I wanted. The closest I came showed more answers that I wanted, i.e. answers connected to the requirement, but not the module. In the image I shared earlier, SFBK_UID.2 had two answers in the output table, which it should not.

I changed my approach.

I have conducted three tables which should give me my table if joined correctly.

The first table finds all requirements related to the module in focus:

Code: [Select]

SELECT req.Name as reqName
FROM t_object modul
INNER JOIN t_connector modRels ON modRels.Start_Object_ID = modul.Object_ID
INNER JOIN t_object req ON req.Object_ID = modRels.End_Object_ID
WHERE req.Stereotype = 'reqStereo' AND modul.Object_ID = #OBJECTID#


Code: [Select]

reqName;
SFBK_UID.2;
SFBK_UID.1;
SFBK_AUT.1;

The second table finds all answers related to the module;

Code: [Select]

SELECT ans.Note AS ansNote
FROM t_object modul
INNER JOIN t_connector modRels ON modRels.Start_Object_ID = modul.Object_ID
INNER JOIN t_object ans ON ans.Object_ID = modRels.End_Object_ID
WHERE ans.Stereotype = 'Concern' AND modul.Object_ID = #OBJECTID#


Code: [Select]

ansNote;
Note on answer for SFBK_UID.2;
Note on answer for SFBK_AUT.1;
Note on answer related the module but no req;

The third table finds all answers related to the requirement;

Code: [Select]

SELECT ans.Note AS ansNote
FROM t_object req
INNER JOIN t_connector reqRels ON reqRels.End_Object_ID = req.Object_ID
INNER JOIN t_object ans ON ans.Object_ID = reqRels.Start_Object_ID
WHERE ans.Stereotype = 'Concern' AND req.Object_ID = #OBJECTID#


Code: [Select]

ansNote;
Note on answer related to module in focus;
Note on answer related to another module;
Note on answer related to yet another module;


So how do I join them?

Column 1:
All entries from table 1

Column 2:
All entries where table 2 and 3 match, if no match - leave blank.


This is probably a lot easier than I think, and I thank you in advance Geert 

[Hurra]

Wait..

is this it? 

Code: [Select]

select rq.Name AS rqName, COALESCE(ans.ansNote, 'NO DOC') AS ansNote
from t_object modul
inner join t_connector mrq on mrq.Start_Object_ID = modul.Object_ID
inner join t_object rq on rq.Object_ID = mrq.End_Object_ID and rq.Stereotype = 'reqStereo' -- inner join with requirement
left join (select rqa.End_Object_ID, answer.Name as ansName, answer.Note as ansNote
from t_connector rqa
inner join t_object answer on answer.Object_ID = rqa.Start_Object_ID
where answer.Stereotype = 'ansStereo') ans on ans.End_Object_ID = rq.Object_ID -- left join with answer
WHERE modul.Object_ID = #OBJECTID#
ORDER BY rqName ASC


[Hurra]

For those who are interested:

Code: [Select]

SELECT reqTable.reqName, ansTable.ansName AS ansName, COALESCE(ansTable.ansNote, 'DOCUMENTATION MISSING: NO ANSWER') AS ansNote
FROM
-- finds all related reqs
(SELECT modul.Name AS modName, req.Name AS reqName
FROM t_object modul
INNER JOIN t_connector modRels ON modRels.Start_Object_ID = modul.Object_ID
INNER JOIN t_object req ON req.Object_ID = modRels.End_Object_ID
WHERE req.Stereotype = 'reqStereo' AND modul.Object_ID = #OBJECTID#) reqTable
LEFT JOIN
(SELECT reqAnsTable.reqName as reqName, reqAnsTable.ansName AS ansName, reqAnsTable.ansNote AS ansNote
FROM
-- finds all answers related to module
(SELECT modul.Name AS modName, ans.Name AS ansName, ans.Note AS ansNote
FROM t_object modul
INNER JOIN t_connector modRels ON modRels.Start_Object_ID = modul.Object_ID
INNER JOIN t_object ans ON ans.Object_ID = modRels.End_Object_ID
WHERE ans.Stereotype = 'ansStereo' AND modul.Object_ID = #OBJECTID#) modAnsTable
LEFT JOIN
-- finds all reqs with an answer, and the answer
(SELECT req.Name as reqName, ans.Name AS ansName, ans.Note AS ansNote
FROM t_object req
INNER JOIN t_connector reqRels ON reqRels.End_Object_ID = req.Object_ID
INNER JOIN t_object ans ON ans.Object_ID = reqRels.Start_Object_ID
WHERE ans.Stereotype = 'ansStereo') reqAnsTable
ON modAnsTable.ansName = reqAnsTable.ansName) ansTable
ON reqTable.reqName = ansTable.reqName
ORDER BY reqName ASC

As far as I can tell this is what I'm looking for.. Hopefully I won't find any misbehavior 

Thank you for your inputs, especially Geert!