Database Query Help

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 425,934 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 1,626 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 981 | Replies: 2

•

Join Date: Jun 2007

Posts: 2

Reputation: mniece is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

mniece

Offline

Newbie Poster

Database Query Help

Jun 1st, 2007

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

•

Join Date: May 2007

Location: Oregon

Posts: 57

Reputation: Stylish is an unknown quantity at this point

Rep Power: 2

Solved Threads: 3

Stylish

Offline

Junior Poster in Training

Re: Database Query Help

Jun 2nd, 2007

Help with Code Tags

(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

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

I NEED AN ADULT!

•

Join Date: Jun 2007

Posts: 2

Reputation: mniece is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

mniece

Offline

Newbie Poster

Database Query Help

Jun 2nd, 2007

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 6:47 am.