user380432 user380432 - 6 months ago 7
SQL Question

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?

Answer

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  
Comments