NEED A PRO'S HELP -- Complex Join??
I have 3 tables, first table has id
table1
id
====
1
2
3
4
etc...
table2 has costing referencing id in table 1 and it may not have any costings for some values in table 1
table2
table1.id | cost
================
1.............20
1.............15
1.............11
3.............40
3.............8
table3 is the same but has receipts
table1.id | receipts
====================
1.............10
2.............9
2.............3
2.............4
Can I sum(cost)-sum(receipts) and join this value on to table 1 in a single query?
So somehow I have join table like this:
table1.id | table2.cost | table3.receipts
=========================================
1.............20.............10
1.............15.............NULL
1.............11.............NULL
2.............NULL...........9
2.............NULL...........3
2.............NULL...........4
3.............40.............NULL
3.............8..............NULL
4.............NULL...........NULL
Then I want to group on table1.id and sum the other 2 columns so I get
table1.id | sum(table2.cost) | sum(table3.receipts)
===================================================
1.............46.................10
2.............0 (or NULL)........16
3.............48.................0 or (NULL)
4.............0 (or NULL)........0 or (NULL)
IS IT POSSIBLE TO BUILD A QUERY TO DO THIS? I HAVE TRIED LEFT RIGHT INNER OUTER GROUPINGS ETC AND I CANT WORK IT OUT!
IS THERE A PRO WHO KNOWS?
Subject
Written By
Posted
NEED A PRO'S HELP -- Complex Join??
November 29, 2007 07:35PM
November 29, 2007 11:46PM
December 06, 2007 10:31AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.