Need help with this SQL....

Have DIVISION, REPORT, DIVISION_REPORT and VERSION table. Relationships are:
DIVISION.division_id = DIVISION_REPORT.division_id, DIVISION_REPORT.report_id = REPORT.report_id and REPORT.report_id = VERSION.report_id (and finally VERSION.version_id for PK). So division can
have many reports and reports can be assigned to many divisions. Each report can have many versions. REPORT table has "max_display" field to be used to only return "max_display" VERSIONs per each report. VERSION has "version_no" field which numbers the versions incrementally in increasing order.

Based on the data model I presented above, if the user passed as an input the division name ("DIVISION.division_nm"), I need to return all the VERSIONs per EACH REPORT in that DIVISION. Only the latest REPORT.max_display number of VERSIONs have to be returned per EACH report in that DIVISION (VERSION.version_no DESC). So if the DIVISION has 5 reports, and each
report has N number of version in VERSION table, per each REPORT.max_display for that report, I need to only return REPORT.max_display number of versions per each report.

It sounds easy, but I just can't put my finger at it. The easy part would be to get all the versions per each report - you can join all the way through from DIVISION to VERSION. But the hard part is - how to limit number of VERSION rows returned based on REPORT.max_display field per -each report- in a division?

Can you/anyone help...?

Darta Received on Tue Jul 29 2003 - 18:11:21 CEST