SQL Query Question

From: Jason Mowat <jmowat_at_digitalpraxis.com>
Date: Mon, 26 Mar 2001 20:35:55 GMT
Message-ID: <LKNv6.1953$4x6.35628@news2.mts.net>

Greets,

I am attempting to design a query, but I am unsure how to do it in the most efficient way. I hope somebody can point me in the right direction.

Summary: I have a collection of tables that represent reports avaiable to users. I have some tables that represent the reports that users can have access to, and I have tables that represent the actual reports that have been archived to a filesystem.

I would like to select all of the most current reports, including it's archive properties, but only if a user has access to it.

Logical Layout:

tbl_rpt

report_id

tbl_access

access_id
user_id

tbl_access_rpt (linked to tbl_access 1-1)

access_id
report_id

tbl_archive

archive_id
absolute_path
archive_date

tbl_archive_rpt (linked to tbl_archive 1-1)

archive_id
report_id
report_date
page_count

I hope that this example is easy enough to follow! Basically, a user can have access to many reports, all of which must exist in tbl_rpt. A report can be archived many times, in a specific directory on a system for a specific date. The archive itself has a date that it was archives, and in the case of archived reports, there in a report_date. This is the date of the report.

Now, some of the SQL is simple enough:

// Select all reports that a user has access to select tbl_access_rpt.report_id
from tbl_access, tbl_access_rpt, tbl_rpt where tbl_access.access_id = tbl_access_rpt.access_id and tbl_access_rpt.report_id = tbl_rpt.report_id and user_id = 'jmowat'

// Get the archive details of the reports select archive_id, absolute_path, report_id, report_date, page_count from tbl_archive, tbl_archive_rpt, tbl_rpt where tbl_archive.archive_id = tbl_archive_rpt.archive_id and tbl_archive_rpt.report_id = tbl_rpt.report_id

// Select the most recent reports on archive select report_id, max(report_date)
from tbl_archive_rpt
group by report_id

For example, assume the data looks as follows in tbl_archive_rpt

1,10,Feb 1
2,20,Jan 1
3,30,Feb 1
4,10,Jan 1
5,20,Apr 1

My query would return:

10,Feb 1
30,Feb 1
20,Apr 1

These are the most recent reports, grouped by report id.

Now, to restate my question: how do I link up the report id's that I have access to (the first query) with the full archive details (the second query) for only the most recent reports (the third query)?

I have been playing around with it, but I am not having that much luck. Any suggestions would be more than welcome!

TIA,
Jason Mowat Received on Mon Mar 26 2001 - 14:35:55 CST