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...
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.
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
