SQL & PL/SQL

Hey everyone!

I'm having some trouble trying to create a SQL query.

I have the following tables:

--Table Products: Product list
CREATE TABLE PRODUCTS(
PRODUCT_ID INTEGER NOT NULL,
CONSTRAINT "PK_PRODUCTS" PRIMARY KEY("PRODUCT_ID")
);

--Table Orders: Product orders
CREATE TABLE ORDERS(
ORDER_ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL, /*FK PRODUCTS(PRODUCT_ID)*/
CONSTRAINT "PK_ORDERS" PRIMARY KEY("ID_PEDIDO")
);

--Table Orders_Approval: Register when an order is approved
CREATE TABLE ORDERS_APPROVAL(
ORDER_ID INTEGER NOT NULL, /*FK ORDERS(ORDER_ID)*/
APPROVAL_DATE DATE NOT NULL,
USER_ID INTEGER,
CONSTRAINT "PK_ORDERS_APPROVAL" PRIMARY KEY("ORDER_ID","APPROVAL_DATE")
);

--Table Orders_ApprovalData: Register other details about order approvals
CREATE TABLE ORDERS_APPROVAL_DETAILS(
APPROVALDET_ID INTEGER NOT NULL,
ORDER_ID INTEGER NOT NULL, /*FK ORDERS_APPROVAL(ORDER_ID)*/
APPROVAL_DATE DATE NOT NULL, /*FK ORDERS_APPROVAL(APPROVAL_DATE)*/
CONSTRAINT "PK_ORDERS_APPROVAL_DETAILS" PRIMARY KEY("APPROVALDET_ID")
);

The thing is that an order may ou may not be approved. So, there might be no register for an order in ORDERS_APPROVAL.

Also, an order may have more than one approval.

Here is the data I'm using:

/===========\
| PRODUCTS |
|===========|
|PRODUCT_ID |
|===========|
| 1 |
|===========|
| 2 |
|===========|
| 3 |
\===========/

/========================\
| ORDERS |
|========================|
| ORDER_ID | PRODUCT_ID |
|===========|============|
| 27 | 1 |
|===========|============|
| 28 | 2 |
|===========|============|
| 29 | 3 |
\===========|============/

/=========================================\
| ORDERS_APPROVAL |
|=========================================|
| ORDER_ID | APPROVAL_DATE | USER_ID |
|===========|================|============|
| 27 | 10/01/2009 | 56 |
|===========|================|============|
| 27 | 10/04/2009 | 96 |
|===========|================|============|
| 29 | 10/03/2009 | 77 |
\===========|================|============/

/=============================================\
| ORDERS_APPROVAL_DETAILS |
|=============================================|
| APPROVALDET_ID | ORDER_ID | APPROVAL_DATE |
|================|===========|================|
| 1 | 27 | 10/01/2009 |
|================|===========|================|
| 2 | 27 | 10/04/2009 |
|================|===========|================|
| 3 | 29 | 10/03/2009 |
\================|===========|================/
I need to create a query that return data from all tables, but for orders with more than one approval,
the query must return only the latest record. It is also necessary to return orders that haven't been approved yet.

My first query was like this:

SELECT
*
FROM
ORDERS ORD,
PRODUCTS PROD,
ORDERS_APPROVAL ORDAPPROV,
ORDERS_APPROVAL_DETAILS ORDAPPDET
WHERE
PROD.PRODUCT_ID=ORD.PRODUCT_ID
AND ORDAPPROV.ORDER_ID(+)=ORD.ORDER_ID
AND ORDAPPDET.ORDER_ID(+)=ORDAPPROV.ORDER_ID
AND ORDAPPDET.DATA_APPROVAL_DATE(+)=ORDAPPROV.APPROVAL_DATE

Ps.: I'm using Oracle 8 syntax because this is the server I'll be using. Also, I cannot change database design.

That query returns data from all table. Since I used LEFT JOIN "(+)" to join the orders_approval table with the orders table, I'm able to see even the orders with no approval.

But this way, I'm also seeing the two approvals for order 27 (with the data above, this order has been approved twice). I need to join my tables only to the last approval for each order but is should be a left join so that I can see the orders with no approval.

I tried this way:

SELECT
*
FROM
ORDERS ORD,
PRODUCTS PROD,
ORDERS_APPROVAL ORDAPPROV,
ORDERS_APPROVAL_DETAILS ORDAPPDET
WHERE
PROD.PRODUCT_ID=ORD.PRODUCT_ID
AND ORDAPPROV.ORDER_ID(+)=ORD.ORDER_ID
--Added the line below
AND ORDAPPROV.APPROVAL_DATE=(SELECT MAX(APPROVAL_DATE) FROM ORDERS_APPROVAL WHERE ORDER_ID=ORDAPPROV.ORDER_ID)
AND ORDAPPDET.ORDER_ID(+)=ORDAPPROV.ORDER_ID
AND ORDAPPDET.DATA_APPROVAL_DATE(+)=ORDAPPROV.APPROVAL_DATE

But this way, as I'm specifying that I want only the records with the approval date equals to the latest for each order, I can't see the order without approval.

I tried again, with a left join like this:

SELECT
*
FROM
ORDERS ORD,
PRODUCTS PROD,
ORDERS_APPROVAL ORDAPPROV,
ORDERS_APPROVAL_DETAILS ORDAPPDET
WHERE
PROD.PRODUCT_ID=ORD.PRODUCT_ID
AND ORDAPPROV.ORDER_ID(+)=ORD.ORDER_ID
--Added the line below
AND ORDAPPROV.APPROVAL_DATE(+)=(SELECT MAX(APPROVAL_DATE) FROM ORDERS_APPROVAL WHERE ORDER_ID=ORDAPPROV.ORDER_ID)
AND ORDAPPDET.ORDER_ID(+)=ORDAPPROV.ORDER_ID
AND ORDAPPDET.DATA_APPROVAL_DATE(+)=ORDAPPROV.APPROVAL_DATE

But I get a syntax error. Oracle says it is not possible to make a join with a subquery.

Would anyone know how I can bring all this data?

Thanks in advance.

Edited by: user9936895 on 05/10/2009 09:42 : Tried to correct the sample data layout.