question

BrianBulla-5922 avatar image
0 Votes"
BrianBulla-5922 asked BrianBulla-5922 answered

Inner Join using SQL SMS

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)

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 published

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'))
 )
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BrianBulla-5922 avatar image
0 Votes"
BrianBulla-5922 answered

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!!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @BrianBulla-5922,

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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 published

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.