Sparx Systems Forum

Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: Jezvon on July 20, 2018, 06:14:34 pm

Title: SQL Query to conditionally select a row from a table depending on join existence
Post by: Jezvon 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.

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

Title: Re: SQL Query to conditionally select a row from a table depending on join existence
Post by: qwerty on July 21, 2018, 04:59:58 pm

You should ask that on StackOverflow since it's a SQL question and not an EA specific.

q.

Title: Re: SQL Query to conditionally select a row from a table depending on join existence
Post by: Eve on July 24, 2018, 09:57:16 am

Replace left join with inner join.

Title: Re: SQL Query to conditionally select a row from a table depending on join existence
Post by: qwerty on July 24, 2018, 11:28:47 am

Answered here on StackOverflow (https://stackoverflow.com/questions/51455691/sql-query-to-conditionally-select-a-row-from-a-table-depending-on-join-existence)

q.