Re: SQL Query Question

From: Jason Mowat <jmowat_at_digitalpraxis.com>
Date: Tue, 27 Mar 2001 14:41:28 GMT
Message-ID: <sE1w6.1995$4x6.36121@news2.mts.net>

Greets,

I figured it out. My solution involved aliasing my first table, and referencing the alias in the sub-query to link. SQL as follows:

select a.archive_id, a.report_id, tbl_archive.absolute_path from tbl_archive_rpt a, tbl_archive, tbl_access, tbl_access_rpt where
tbl_archive.archive_id = a.archive_id

and tbl_access.access_id = tbl_access_rpt.access_id
and tbl_access.user_id = 'jmowat'
and tbl_access_rpt.report_id = a.report_id

and a.report_date =
(
  select max(b.report_date)
  from tbl_archive_rpt b
  where b.report_id = a.report_id
  group by b.report_id
)

This query shows all recent report archives for a specific user, and will only show the reports that the specific user has access to.

Cheers,
Jason

"Jason Mowat" <jmowat_at_digitalpraxis.com> wrote in message news:LKNv6.1953$4x6.35628_at_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 Tue Mar 27 2001 - 08:41:28 CST