[Resolvido] Uso de Left Join com Subquery

Pessoal, bom dia.

Estou com um problema para montar uma consulta SQL.

Tenho as seguintes tabelas:

--Tabela Produtos: Relação de produtosCREATE TABLE PRODUTOS(ID_PRODUTO INTEGER NOT NULL,CONSTRAINT "PK_PRODUTOS" PRIMARY KEY("ID_PRODUTO"));--Tabela Pedidos: Relação de produtos requisitadosCREATE TABLE PEDIDOS(ID_PEDIDO INTEGER NOT NULL,ID_PRODUTO INTEGER NOT NULL, /FK PRODUTOS(ID_PRODUTO)/CONSTRAINT "PK_PEDIDOS" PRIMARY KEY("ID_PEDIDO"));--Tabela Pedidos_Aprovacao: Tabela que registra a aprovação dos pedidos.CREATE TABLE PEDIDOS_APROVACAO(ID_PEDIDO INTEGER NOT NULL, /FK PEDIDOS(ID_PEDIDO)/DATA_APROVACAO DATE NOT NULL,ID_USUARIO INTEGER,CONSTRAINT "PK_PEDIDOS_APROVACAO" PRIMARY KEY("ID_PEDIDO","DATA_APROVACAO"));--Tabela Pedidos_Modificados: Guarda outros detalhes referente aos pedidos aprovadosCREATE TABLE PEDIDOS_MODIFICADOS(ID_MODIFICACAO INTEGER NOT NULL,ID_PEDIDO INTEGER NOT NULL, /FK PEDIDOS_APROVACAO(ID_PEDIDO)/DATA_APROVACAO DATE NOT NULL, /FK PEDIDOS_APROVACAO(DATA_APROVACAO)//OUTROS CAMPOS AQUI/CONSTRAINT "PK_PEDIDOS_MODIFICADOS" PRIMARY KEY("ID_MODIFICACAO"));

O grande detalhe é que um pedido pode ou não estar aprovado. E um pedido pode ser aprovado mais de uma vez.

Os dados que estou utilizando para teste são:

/===========\ /========================\ /=========================================\| PRODUTOS | | PEDIDOS | | PEDIDOS_APROVACAO ||===========| |========================| |=========================================||ID_PRODUTO | | ID_PEDIDO | ID_PRODUTO | | ID_PEDIDO | DATA_APROVACAO | ID_USUARIO ||===========| |===========|============| |===========|================|============|| 1 | | 27 | 1 | | 27 | 01/10/2009 | 56 ||===========| |===========|============| |===========|================|============|| 2 | | 28 | 2 | | 27 | 04/10/2009 | 96 ||===========| |===========|============| |===========|================|============|| 3 | | 29 | 3 | | 29 | 03/10/2009 | 77 |\===========/ \===========|============/ \===========|================|============//=============================================\| PEDIDOS_MODIFICADOS ||=============================================|| ID_MODIFICACAO | ID_PEDIDO | DATA_APROVACAO ||================|===========|================|| 1 | 27 | 01/10/2009 ||================|===========|================|| 2 | 27 | 04/10/2009 ||================|===========|================|| 3 | 29 | 03/10/2009 |\================|===========|================/

Eu preciso montar uma query que una todas essas tabelas, mas como um pedido pode ser aprovado mais de uma vez, é necessário que apenas o registro de aprovação mais recente seja trazido na consulta. Também é necessário trazer os pedidos que não estão aprovados.

Inicialmente, a consulta ficou assim:

SELECT *FROM PEDIDOS PED, PRODUTOS PROD, PEDIDOS_APROVACAO PEDAPROV, PEDIDOS_MODIFICADOS PEDMODWHERE PROD.ID_PRODUTO=PED.ID_PRODUTO AND PEDAPROV.ID_PEDIDO(+)=PED.ID_PEDIDO AND PEDMOD.ID_PEDIDO(+)=PEDAPROV.ID_PEDIDO AND PEDMOD.DATA_APROVACAO(+)=PEDAPROV.DATA_APROVACAO

Obs.: Estou usando a sintaxe suportada pelo Oracle 8 para a montagem da consulta SQL pois esse é o banco que será utilizado para o sistema.

A query acima traz todas as tabelas unidas. Como utilizei LEFT JOIN para unir os registros com a tabela de pedidos aprovados e modificados, a consulta retorna também os pedidos que possuem registros nessa tabela.

O problema é que desta forma a consulta também traz dois registros para o ID_PEDIDO 27, pois no exemplo acima, ele foi aprovado duas vezes. Eu na verdade preciso trazer para este pedido apenas a última aprovação dele, ou seja, a de 4/10/2009.

Tentei fazer dessa forma:

SELECT FROM PEDIDOS PED, PRODUTOS PROD, PEDIDOS_APROVACAO PEDAPROV, PEDIDOS_MODIFICADOS PEDMODWHERE PROD.ID_PRODUTO=PED.ID_PRODUTO AND PEDAPROV.ID_PEDIDO(+)=PED.ID_PEDIDO /Adicionei a linha abaixo*/ AND PEDAPROV.DATA_APROVACAO=(SELECT MAX(DATA_APROVACAO) FROM PEDIDOS_APROVACAO WHERE ID_PEDIDO=PEDAPROV.ID_PEDIDO) AND PEDMOD.ID_PEDIDO(+)=PEDAPROV.ID_PEDIDO AND PEDMOD.DATA_APROVACAO(+)=PEDAPROV.DATA_APROVACAO

Mas desta forma, como eu estou especificando que quero que traga apenas o registro com a data mais atual para cada pedido, os pedidos que não foram aprovados não são exibidos.

Tentei alterar essa cláusula para um LEFT JOIN:

SELECT FROM PEDIDOS PED, PRODUTOS PROD, PEDIDOS_APROVACAO PEDAPROV, PEDIDOS_MODIFICADOS PEDMODWHERE PROD.ID_PRODUTO=PED.ID_PRODUTO AND PEDAPROV.ID_PEDIDO(+)=PED.ID_PEDIDO /Adicionei a linha abaixo*/ AND PEDAPROV.DATA_APROVACAO(+)=(SELECT MAX(DATA_APROVACAO) FROM PEDIDOS_APROVACAO WHERE ID_PEDIDO=PEDAPROV.ID_PEDIDO) AND PEDMOD.ID_PEDIDO(+)=PEDAPROV.ID_PEDIDO AND PEDMOD.DATA_APROVACAO(+)=PEDAPROV.DATA_APROVACAO

Mas da forma acima o Oracle não aceita. Diz que não é possível fazer um join com uma subquery.

Alguém saberia alguma forma de montar essa consulta?

Desde já agradeço.