SQL use nested select in middle of inner join

Is it possible to use a select in the middle of joining...

I am trying to do the following:

FROM tblorders o INNER JOIN tblunits u on o.id = u.orderid INNER JOIN ((SELECT ,Min(n.date) as [MinDate] from tblNotes n Where n.test = 'test') te INNER JOIN tblnotes n on te.id = n.id and te.[MinDate] = n.AuditinsertTimestamp) INNER Join tblClient c ON o.ClientId = c.Id 

Basically in the select in the middle of the query it is selecting only the notes with min date. The problem is I need to do this here because I need from tblOrders to be the first table.......

Suggestions?

3 Answers

The INNER JOIN failed because you have a leading comma here:

,Min(n.date) as [MinDate] 

I think you are looking for something like this:

SELECT ... FROM tblorders o INNER JOIN tblunits u on o.id = u.orderid INNER JOIN ( SELECT id, Min(date) as [MinDate] from tblNotes Where test = 'test' group by id ) te <-- not sure what JOIN clause to use here, please post schema INNER JOIN tblnotes n on te.id = n.id and te.[MinDate] = n.AuditinsertTimestamp INNER Join tblClient c ON o.ClientId = c.Id 

You are missing an alias and join condition:

FROM tblorders o INNER JOIN tblunits u on o.id = u.orderid INNER JOIN ((SELECT Min(n.date) as [MinDate] from tblNotes n Where n.test = 'test') te INNER JOIN tblnotes n on te.id = n.id and te.[MinDate] = n.AuditinsertTimestamp) -- missing AS z ON <join conditions haere> INNER Join tblClient c ON o.ClientId = c.Id 

Yes, you can have a Select in a Join.