I'm in need of some help with 3 tables I have with MSSQL. Is there a way to use a single database query to get the results I'm looking for? (See the bottom for an ideal returned recordset.) Thanks!
TABLE: APPLICATION
APP_ID APP_NM GRP_ID
--------------------------------------
1 Script 1 1
2 Script 2 1
3 Script 3 2
TABLE: STATUS
STAT_ID APP_ID STAT_TM STAT_DSC
---------------------------------------------
1 1 4:00pm OK
2 2 4:00pm OK
3 1 4:15pm OK
4 3 4:00pm FAILED
5 2 4:15pm FAILED
6 3 4:15pm OK
TABLE: GROUPS
GRP_ID GRP_NM
--------------------
1 Group 1
2 Group 2
These tables are related as follows:
APPLICATION.APP_ID <---> STATUS.APP_ID
APPLICATION.GRP_ID <---> GROUPS.GRP_ID
You see, the idea is to tell the database:
Give me all the latest 'status' records for 'Group 1' applications.
The ideal query specifically for "Group 1" is to show the following results:
STAT_ID STAT_TM STAT_DSC APPL_ID APPL_NM GRPS_ID
----------------------------------------------------------------------------
5 4:15pm FAILED 2 Script 2 1
Thanks for any help!
-Michael