Hi. Noob here trying to figure out SQL SMS queries. I'm trying to take a query that works in MS Access and use it in SQL Server Management Studio. I start getting syntax errors at the "inner join". If I just run the first select-from-where statement all is good, but after I add the inner join, nothing works. I get a 'syntax error' at the inner join line, and then nothing works after that.
I don't think I'm far off, but i can't seem to figure out what the issue is. Can anyone help me figure this out??
select all_wr_table.INSP_DATE
from works.INSPECTIONS_VALVE_INSPECTION as all_wr_table
where(
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVE_INOPERABLE = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVE_LEAKING = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVE_SPINS = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVE_OTHER = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVEBOX_STRAIGHT = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVEBOX_BROKEN = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVEBOX_ADJUST = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVEBOX_OTHER = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVECHAMBER_REPAIRS_TO_CONCRE = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVECHAMBER_REPAIRS_TO_BRICK = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVECHAMBER_PUMPOUT = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVECHAMBER_OTHER = 'True')) or
((all_wr_table.DATEWORK is null) and (all_wr_table.VALVEBOX_FULL_OF_DEBRIS = 'True'))
)
inner join
(select max(most_recent_Table.INSP_DATE) as MaxOfINSP_DATE, most_recent_Table.FACILITYID
from works.INSPECTIONS_VALVE_INSPECTION as most_recent_Table
group by most_recent_Table.FACILITYID
order by most_recent_Table.FACILITYID)
on
(all_wr_Table.FACILITYID=most_recent_Table.FACILITYID) and (all_wr_Table.INSP_DATE = most_recent_Table.MaxOfINSP_DATE)