troubleshooting Question

"Where before a join' to filter site

Avatar of ajosephson
ajosephson asked on
Microsoft SQL ServerSQL
2 Comments1 Solution1374 ViewsLast Modified:
I need to filter  W1.Site = 'Sydney' from table WS_devices_main W1.

How can this be achieved with the following SQL sentence -> (ie where fo i put the 'where W1.Site='Sydney')

SQL = "SELECT W1.DeviceId, W1.PrimaryUser, A5.ItemValue1,W1.Workstation_Name, W1.Serial_Number, W1.Model, W1.Form_Factor, W1.Site, W1.Site_Area, W1.Status,A1.DateTimeDB,A1.RecordID, A2.ItemValue1, A3.ItemValue1, A6.ItemValue1 FROM WS_devices_main W1 "

SQL = SQl & "left join WinAudi2 A1 on A1.RecordID = (select max(B1.RecordID) from WinAudi2 B1 "
SQL = SQl & "where B1.Computer = W1.Workstation_Name)"

SQL = SQl & "left join WinAudi2 A2 on  A2.RecordID = (select max(B2.RecordID) from WinAudi2 B2 "
SQL = SQl & "where B2.Computer = W1.Workstation_Name and B2.ItemName='Total Memory') "

SQL = SQl & "left join WinAudi2 A3 on  A3.RecordID = (select max(B3.RecordID) from WinAudi2 B3 "
SQL = SQl & "where B3.Computer = W1.Workstation_Name and B3.ItemName='Total Hard Drive') "

'SQL = SQl & "left join WinAudi2 A4 on  A4.RecordID = (select max(B4.RecordID) from WinAudi2 B4 "
'SQL = SQl & "where B4.Computer = W1.Workstation_Name and B4.ItemName='Computer Name') "

SQL = SQl & "left join WinAudi2 A5 on  A5.RecordID = (select max(B5.RecordID) from WinAudi2 B5 "
SQL = SQl & "where B5.Computer = W1.Workstation_Name and B5.ItemName='User Name') "

SQL = SQl & "left join WinAudi2 A6 on  A6.RecordID = (select max(B6.RecordID) from WinAudi2 B6 "
SQL = SQl & "where B6.Computer = W1.Workstation_Name and B6.ItemName='Processor Description') "

SQL = SQl & "group by W1.DeviceId,W1.Workstation_Name,A1.DateTimeDB,A1.RecordID, W1.PrimaryUser,W1.Serial_Number,W1.Model,A2.ItemValue1, A3.ItemValue1,A5.ItemValue1,A6.ItemValue1, W1.Form_Factor, W1.Site, W1.Site_Area, W1.Status,A1.RecordID"

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros