quote:

---

Originally posted by mqh7

Not sure I follow you. If I take each inner SELECT and run them they work. It is only when I join them do I get errors. Where do I need the alias and which ON is wrong?

---

USE SMS_NA1
GO

SELECT
	rid1.ResourceID
	,rid2.ResourceID
FROM 
	v_GS_computer_system rid1 
		INNER JOIN 
	v_GS_softwareFile rid2 --ON rid1.ResourceId = rid2.ResourceID
	INNER JOIN -- should fix the two below errors now you specificed the above join
	( -- ERROR on this
	SELECT -- ERROR on this
		a.Name0 AS [Machine Name],
		b.SiteCode,c.FileVersion AS [Java],
		d.Operating_System_Name_and0,
			c.FilePath,
		v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HWScan],
		DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) AS [Days Since Last HWScan]
	FROM 
		v_GS_SoftwareFile c 
			INNER JOIN 
		v_GS_SYSTEM a ON c.ResourceID = a.ResourceID
			INNER JOIN 
		v_R_System d ON a.ResourceID = d.ResourceID
			INNER JOIN
		 v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
			INNER JOIN 
		v_GS_WORKSTATION_STATUS ON v_GS_WORKSTATION_STATUS.ResourceID = a.ResourceID
	WHERE (c.FileName = 'java.exe')
	AND (c.FileVersion LIKE '6.0.%' )
	AND (c.FilePath LIKE 'c:\program files%')
	AND (a.Name0 NOT LIKE 'N1%')
	AND (a.Name0 NOT LIKE 'N2%')
	) t1 -- ERROR on the ) --ON t1..... = rid1/rid2....
		INNER JOIN
	(
	SELECT 
		gs.Name0 AS [PC Name],
		gs.Domain0 AS [Domain],
		gs.Manufacturer0 AS [Manufacturer],
		gs.Model0 AS [Model],
		gs.UserName0 AS [User Name],
		sw.LastScanDate AS [Last SW Scan],
		datediff(dd, LastScanDate, GETDATE()) AS [DaysSinceLastScan]

	FROM
		v_GS_COMPUTER_SYSTEM gs 
			INNER JOIN 
		v_GS_LastSoftwareScan sw ON gs.ResourceID = sw.ResourceID
	WHERE 
		gs.Name0 LIKE '%-TC-7%'AND DateDiff(dd, sw.LastScanDate, GETDATE()) >=31
	)-- the derived table needs a tame and the join I.E T2 ON T2.RsourceID = T1.ResourceID

quote:

---

Originally posted by mqh7

Your code gives no syntax errors but it fails when I run it. Saying there is an error on ')' line 51.

---