Inner Join using SQL SMS

Question

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)

Answer 1

You need to put INNER JOIN between FROM and WHERE. And also you cannot use ORDER BY in the subquery. Try this:

select all_wr_table.INSP_DATE
from works.INSPECTIONS_VALVE_INSPECTION as all_wr_table
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
) as most_recent_Table
on 
(all_wr_Table.FACILITYID=most_recent_Table.FACILITYID) and (all_wr_Table.INSP_DATE = most_recent_Table.MaxOfINSP_DATE)
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'))
)

Answer 2

Try something like this:

select all_wr_table.INSP_DATE
from works.INSPECTIONS_VALVE_INSPECTION as all_wr_table
cross apply (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) mr
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'))
)
and
  (all_wr_Table.FACILITYID = mr.FACILITYID and all_wr_Table.INSP_DATE = mr.MaxOfINSP_DATE)

Show the error messages and try simplifying the condition.

Answer 3

Hi @Brian Bulla ,

Welcome to the Microsoft Q&A Forum!

Your question is related to TSQL. My colleague helped you add the TSQL tag so that the people on the TSQL forum can help you.

The physical order of the basic statements in sql is as follows:

    SELECT <select_list>  
    FROM [left_table]  
    JOIN [right_table] ON <join_condition>  
    WHERE <where_condition>  
    GROUP BY <group_by_list>  
    HAVING <having_condition>  
    ORDER BY <order_by_list>   

Operators such as join, apply, pivot, etc. should be after from and before where.

In addition to the problems mentioned by Guoxiong,another problem is that table expressions must have aliases. The so-called table expressions are replaced by an expression where they originally belonged to the table.
The statement after your inner join is a table expression, and it must have an alias:

    inner join  
    (select max(INSP_DATE) as MaxOfINSP_DATE,FACILITYID  
    from works.INSPECTIONS_VALVE_INSPECTION   
    group by FACILITYID) as most_recent_Table   

the conditions behind where can also be simplified, please refer to:

select all_wr_table.INSP_DATE  
from works.INSPECTIONS_VALVE_INSPECTION as all_wr_table  
inner join  
(select max(INSP_DATE) as MaxOfINSP_DATE, FACILITYID  
from works.INSPECTIONS_VALVE_INSPECTION   
group by FACILITYID) most_recent_Table  
on  
(all_wr_Table.FACILITYID=most_recent_Table.FACILITYID) and (all_wr_Table.INSP_DATE = most_recent_Table.MaxOfINSP_DATE)  
where  
(all_wr_table.DATEWORK is null) and ((all_wr_table.VALVE_INOPERABLE = 'True') or  
(all_wr_table.VALVE_LEAKING = 'True') or  
(all_wr_table.VALVE_SPINS = 'True') or  
(all_wr_table.VALVE_OTHER = 'True') or  
(all_wr_table.VALVEBOX_STRAIGHT = 'True') or  
(all_wr_table.VALVEBOX_BROKEN = 'True') or  
(all_wr_table.VALVEBOX_ADJUST = 'True') or  
(all_wr_table.VALVEBOX_OTHER = 'True') or  
(all_wr_table.VALVECHAMBER_REPAIRS_TO_CONCRE = 'True') or  
(all_wr_table.VALVECHAMBER_REPAIRS_TO_BRICK = 'True') or  
(all_wr_table.VALVECHAMBER_PUMPOUT = 'True') or  
(all_wr_table.VALVECHAMBER_OTHER = 'True') or   
(all_wr_table.VALVEBOX_FULL_OF_DEBRIS = 'True'))  
order by most_recent_Table.FACILITYID  

Since you did not provide sample data, the above code has not been tested. If it does not solve your problem, please provide a minimal example (including the create and insert into statements of the tables involved in the code) and the results you expect.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Thanks everybody. Each solution gave me the answer I was looking for, so that is great. I will have to "Accept" the first one though. That wouldn't be fair otherwise.

But thanks for all the great feeback!!