SQL Query to conditionally select a row from a table depending on join existence

[Jezvon]

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

[qwerty]

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

q.

[Eve]

Replace left join with inner join.

[qwerty]

Answered here on StackOverflow

q.