Database Query Help

Expand Post »

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

Similar Threads

SQL statement to query user input?? in MS Access and FileMaker Pro
Tooltip for ASP database query result in ASP
Error Executing Database Query on server in ColdFusion
Using PHP to connect to remote MSSQL database in PHP
conditional database query in ColdFusion

mniece

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jun 2007

Permalink

Jun 2nd, 2007

Re: Database Query Help

MS SQL Syntax (Toggle Plain Text)
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
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

Last edited by Stylish; Jun 2nd, 2007 at 3:25 am.

Stylish

Reputation Points: 44

Solved Threads: 19

Junior Poster

Offline

148 posts
since May 2007

Permalink

Jun 2nd, 2007

Database Query Help

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

Last edited by mniece; Jun 2nd, 2007 at 7:47 am.

mniece

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jun 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: cross data please heslp

Next Thread in MS SQL Forum Timeline: Seemingly Simple Issue

DaniWeb Message

Cancel Changes

User Name
Password