Not in subquery one to one relation


My database has below one to one relationship between two tables

I need all signature id from signature table which are not used in Timesheet table so what will be SQL query for that ?

Thanks & Regards

Mithlesh Singh

Hi Mithlesh,

query would be:

Select {signature}.[id] from {signature} where {signature}.[id] not in (Select {timesheet}.[signatureid] from {timesheet}).

Hope this helps.


Manish Jawla

Two comments:

1) I would use an Aggregate instead of an SQL query. Use Signature and Timesheet, and left-join them together (Signature With or Without Timesheet). Then have a Filter that says TimeSheet.Id = NullIdentifier(). In general, only use a SQL query if you can't get away with an Aggregate.

2) If there's really a 1:1 relationship between two Entities, and the second Entity (here Signature) is only used in relation to the specific other Entity, consider using naming indicating that, and have an Id that's of type Identifier of <other Entity>. In this case, you'd name the Signature table Timesheet_Signature instead, and have its Id of type Timesheet Identifier. In that way, you can ensure there will not be any "stray" records like the ones you want to detect with your query.