Show Posts
1
Automation Interface, Add-Ins and Tools / SQL Query to conditionally select a row from a table depending on join existence
« on: July 20, 2018, 06:14:34 pm »
I have a query that joins 3 tables and I am trying to retrieve a conditional set of rows depending on whether the record exists in the third table.
If there is a match in the 3rd table then I just want the matching records from the 1st table. if there isn't a match from the 3rd table then I just want one record from the first table.
This produces the following type of result, but the ones highlight with the > ... < are the actual rows that are required.
If there is a match in the 3rd table then I just want the matching records from the 1st table. if there isn't a match from the 3rd table then I just want one record from the first table.
Code: [Select]
select o.Object_ID,
reqCon.Connector_ID,
req.Object_ID as Requirement_ID
from t_object o
left join t_connector reqCon on reqCon.End_Object_ID = o.Object_ID and reqCon.Stereotype = 'deriveReqt'
left join t_object req on reqCon.Start_Object_ID = req.Object_ID and req.Stereotype = 'functionalRequirement'
This produces the following type of result, but the ones highlight with the > ... < are the actual rows that are required.
| Object_ID | Connector_ID | Requirement_ID |
| 40936 | 43259 | |
| 40936 | 43260 | |
| >40936 | 43299 | 38013< |
| 40943 | 43264 | |
| 40943 | 43265 | |
| >40943 | 43298 | 38014< |
| >44088 | 46245< | |
| 44088 | 46246 | |
| >42669 | 44655< | |
| 42669 | 44656 | |
| >42670 | 44657< | |
| 42670 | 44658 |