Orange Coast Database Forums

Post Info TOPIC: SQL


Directeur

Status: Offline
Posts: 42
Date:
SQL


Main SQL discussion list. Others will be created over time for Transact and Oracle specifics. Meanwhile you can include all SQL (except Access JET SQL) posts here.



-- Edited by danhdurso on Wednesday 10th of August 2011 07:02:32 AM



__________________


Directeur

Status: Offline
Posts: 42
Date:
Learning SQL with Magets


Magnets? I never heard of them, either. But here is a link to a really unusual learning tool - virtual "sticky magnets" from Head First Books. You rearrange them to create an SQL statement.

Have fun!

__________________


Newbie

Status: Offline
Posts: 1
Date:
RE: SQL


Have two tables UseCases and UseCaseNotes, where UseCases is the parent table. Trying to get the name, owner, start date, and status along with the latest Note Date, and the associated resource and description.

I started with the query below, but am having some dificulty in completing it. Anyone who can help?

SELECT UseCases.Name, UseCases.Description, UseCases.Owner, UseCases.Status, UseCases.StartDate, MAX(UseCaseNotes.NoteDate), UseCaseNotes.Resource, UseCaseNotes.Description
FROM UseCases, UseCaseNotes
WHERE UseCases.UseCaseID=UseCaseNotes.UseCaseID
ORDER BY UseCases.Owner;

Thanks for the assist!

A


__________________


Directeur

Status: Offline
Posts: 42
Date:

Here is  a script that uses grouping to only pull up the latest notes records. I took the liberty of simplifying the tables and used the actual use case name for the PK and FK to keep the number of fields to a minimum for my test build. The trick is to use a sub query to select the notes fields for the latest notes record. I tried it in Access and it should work in enterprise SQL's too.

SELECT useCases.name, useCases.description, useCases.start_date, useCaseNotes.description, useCaseNotes.note_date
FROM useCases INNER JOIN useCaseNotes ON useCases.name = useCaseNotes.useCaseName
WHERE (((useCaseNotes.note_date) In (SELECT Max(UseCaseNotes.note_date) AS MaxOfnote_date
FROM UseCaseNotes
GROUP BY UseCaseNotes.useCaseName;)));

HTH,

--Dan

__________________


Directeur

Status: Offline
Posts: 42
Date:

After thinking about this one I wonder if it wouldn't be better done as a correlated subquery. Although it works the way it is, it should perform faster if correlated. Any one want to take a stab at it?

--Dan

__________________
Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.

Tweet this page Post to Digg Post to Del.icio.us
Members Login
Username 
 
Password 
    Remember Me  


Create your own FREE Forum
Report Abuse
Powered by ActiveBoard