PL/SQL - How to pull data from 3 tables based on latest created date
- by Nancy
Hello,
I'm hoping someone can help me as I've been stuck on this problem for a few days now. Basically I'm trying to pull data from 3 tables in Oracle: 1) Orders Table 2) Vendor Table and 3) Master Data Table.
Here's what the 3 tables look like:
Table 1: BIZ_DOC2 (Orders table)
OBJECTID (Unique key)
UNIQUE_DOC_NAME (Document Name i.e. ORD-005)
CREATED_AT (Date the order was created)
Table 2: UDEF_VENDOR (Vendors Table):
PARENT_OBJECT_ID (This matches up to the ObjectId in the Orders table)
VENDOR_OBJECT_NAME (This is the name of the vendor i.e. Acme)
Table 3: BIZ_UNIT (Master Data table)
PARENT_OBJECT_ID (This matches up to the ObjectID in the Orders table)
BIZ_UNIT_OBJECT_NAME (This is the name of the business unit i.e. widget A, widget B)
Note: The Vendors Table and Master Data do not have a link between them except through the Orders table.
I can join all of the data from the tables and it looks something like this:
Before selecting latest order date:
ORD-005 | Widget A | Acme | 3/14/10
ORD-005 | Widget B | Acme | 3/14/10
ORD-004 | Widget C | Acme | 3/10/10
Ideally I'd like to return the latest order for each vendor. However, each order may contain multiple business units (e.g. types of widgets) so if a Vendor's latest record is ORD-005 and the order contains 2 business units, here's what the result set should look like by the following columns: UNIQUE_DOC_NAME, BIZ_UNIT_OBJECT_NAME, VENDOR_OBJECT_NAME, CREATED_AT
After selecting by latest order date:
ORD-005 | Widget A | Acme | 3/14/10
ORD-005 | Widget B | Acme | 3/14/10
I tried using Select Max and several variations of sub-queries but I just can't seem to get it working. Any help would be hugely appreciated!
