How to add another field to this query.

Brian Bulla 21 Reputation points
2023-04-06T14:46:30.72+00:00

Hi, So this query will select all of the most recent inpsections from a table that have a valve marked as "INOPERABLE". There are two tables involved....a table of valves (WAT__CONTROLVALVE) and a table of inspections (INSPECTIONS__VALVE_INSPECTION). When I run the query I get the results I expect, but I am trying to figure out how to add some fields from the WAT_CONTROLVALVE table into the final query results. For example, I need the field names LIFECYCLESTATUS in the final output. Can anyone help? Thanks.

 select all_wr_table.*
 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 inner join works.WAT_CONTROLVALVE on most_recent_Table.FACILITYID = works.WAT_CONTROLVALVE.FACILITYID
     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'))
 )
 )
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,899 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Brian Bulla 21 Reputation points
    2023-04-06T15:59:51.2833333+00:00

    OK, I eventually figured this out, with the help of MS Access query builder GUI.

    SELECT all_wr_table.*, works.WAT_CONTROLVALVE.LIFECYCLESTATUS
    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 inner join works.WAT_CONTROLVALVE on most_recent_Table.FACILITYID = works.WAT_CONTROLVALVE.FACILITYID
    		group by most_recent_Table.FACILITYID
    	)  AS most_recent_Table 
    	ON 
    	(all_wr_table.INSP_DATE = most_recent_Table.MaxOfINSP_DATE) AND (all_wr_table.FACILITYID = most_recent_Table.FACILITYID)
    ) 
    INNER JOIN 
    	works.WAT_CONTROLVALVE ON all_wr_table.FACILITYID = works.WAT_CONTROLVALVE.FACILITYID
    	WHERE 
    	(
    		((all_wr_table.DATEWORK) Is Null) AND ((all_wr_table.VALVE_INOPERABLE)='True')
    	);
    
    
    0 comments No comments