Database Query Help

[mniece]

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

[Stylish]

SELECT STATUS.*
FROM STATUS, APPLICATION, GROUPS
WHERE GRP_NAME = 'Group 1' AND GROUPS.GRP_ID = APPLICATION.GRP_ID AND APPLICATION.APP_ID = STATUS.APP_ID

[mniece]

I appreciate your reply. However, that query is identical to what I've tried and it returns back all the records from the status table for a given group. The idea is to only get the latest, singe record for each application.

The example above actually had a mistake in it. The ideal returned query would show this:
STAT_ID STAT_TM STAT_DSC APPL_ID APPL_NM GRPS_ID
-------------------------------------------------------------------------------
3 4:15pm "OK" 1 "Script 1" 1
5 4:15pm "FAILED" 2 "Script 2" 1

-Michael