-
Hi all,
I have a problem.
I have a SUBMITTAL record, which has SUBMITTAL_ID and VERSION_NBR as part of the PK.
As a child table, I have SUBMITTAL_LINE_ITEM, which is related to SUBMITTAL_ID and VERSION_NBR as FK. There is a 1-m relationship between these 2 tables.
How do i write a query to bring back only the records that relate to the highest VERSION_NBR for a SUBMITTAL_ID in the SUBMITTAL table?
eg:
SUBMITTAL Table
***************
SUMBITTAL_ID VERSION_NBR
1 1
1 2
1 3
2 1
2 2
SUBMITTAL_LINE_ITEM Table
************************
SUBMITTAL_LINE_ITEM_ID SUMBITTAL_ID VERSION_NBR
1 1 1
2 1 1
3 1 1
4 1 2
5 1 2
6 1 2
7 1 3
8 2 1
9 2 2
In this example, I would return SUBMITTAL_LINE_ITEM 7 for SUBMITTAL_ID 1, VERSION_NBR 3 and SUBMITTAL_LINE_ITEM 9 for SUBMITTAL_ID 2, VERSION_NBR 2 - FORGETTING ABOUT THE REST
THANKS IN ADVANCE.
-ka
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|