NEED A PRO'S HELP -- Complex Join??
Posted by: Richard Flynn
Date: November 29, 2007 07:35PM

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?

Options: ReplyQuote


Subject
Written By
Posted
NEED A PRO'S HELP -- Complex Join??
November 29, 2007 07:35PM


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.