Oracle 8 Left Join Not Working

[mrgulic]

I have the old Oracle 8 database that I am trying to get some data off but I can't quite get the query right.

SELECT TO_CHAR(TRANSACTION0.DOCUMENT_DATE, 'YYYY-MM') AS MyDate, TRANSACTION0.FK_TRANSMISSIONFK, TRADING_PARTNER.NAME, 
TRANSACTION0.DOCUMENT_NAME, TRANSACTION0.DOCUMENT_ID, TRANSACTION0.FK_TRANSMISSIONGS1, DOCUMENT.TRANSLATOR_KEY, 
DOCUMENT.TRANSLATOR_ALIAS, COUNT(DOCUMENT_ID)
FROM TRANSACTION0, TRADING_PARTNER, DOCUMENT
WHERE (TO_CHAR (DOCUMENT_DATE, 'YYYY-MM-DD')) >= '2010-03-01'
AND FK_TRANSMISSIONPRO = 'P' 
AND TRANSACTION0.FK_TRANSMISSIONFK (+) = TRADING_PARTNER.APPLIC_PARTNER_COD 
AND TRADING_PARTNER.APPLIC_PARTNER_COD (+) = DOCUMENT.FK_TRADING_PARTAPP
AND TRANSACTION0.FK_TRANSMISSIONFK = '006173082'
GROUP BY TO_CHAR(TRANSACTION0.DOCUMENT_DATE, 'YYYY-MM'), TRANSACTION0.FK_TRANSMISSIONFK, TRANSACTION0.DOCUMENT_NAME, 
TRANSACTION0.DOCUMENT_ID, TRANSACTION0.FK_TRANSMISSIONGS1, TRADING_PARTNER.NAME, DOCUMENT.TRANSLATOR_KEY, DOCUMENT.TRANSLATOR_ALIAS
ORDER BY TRADING_PARTNER.NAME

In english:

I want to get All the records from TRANSACTION0, any records from TRADING_PARTNER that are equal to TRANSACTION0 and all records from DOCUMENT that a equal to TRADING_PARTNER.

Perhaps I have the join wrong because when the results should be 3 records (given the example data), i end up with 18.

I am sure its from the join of the last table (DOCUMENT) as there are 6 records in that table that are unique to the partner being querried.

Thanks for any help or suggestions.

 

[Dagon]

It would help if we could see the example data. It sounds as if the cardinality of your joins isn't one-to-one and you are getting a cartesian product.

For Oracle-related work, contact me through Linked-In.

 

[SantaMufasa]

...you are getting a cartesian product...

Isn't the beer they make at Olympia Brewing Company of Tumwater Washington a cartesian product? <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,


As usual, Santa, you are a wellspring of humor.





To Paraphrase:"The Help you get is proportional to the Help you give.."