aspose file tools*
The moose likes Oracle/OAS and the fly likes Inner join as a sub Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Inner join as a sub Query" Watch "Inner join as a sub Query" New topic
Author

Inner join as a sub Query

Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
I am providing all the details (CREATE/INSERT queries ) so that one can run it.
I want to sum a column as per id of two different tables and join it and produce a single row



This is what i want
id = 1
sum(A.amount) = 1250
sum(B.amount) = 2070
(offcourse, a single row)

This gives wrong data :


The wrong output is

id = 1
sum(A.amount) = 5000
sum(B.amount) = 6210
(offcourse, a single row)

The reason for wrong output is, that the no of rows with id 1 in table A differs from the no of rows with id 1 in table B

I tried



But it gives "ORA - 00907 - missing right parenthesis" error

How should i proceed?


kunal
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

A for providing the scripts!

Your observation regarding your first query
Kunal Lakhani wrote:
The reason for wrong output is, that the no of rows with id 1 in table A differs from the no of rows with id 1 in table B

is not entirely correct. That query would give the output you expect if and only if both tables contained exactly one row with ID=1. If both tables contained, say, two rows with ID=1, the output would be wrong different from what you want. I'll leave it on you to figure out why is this so.

You're heading in the right direction with the last query, but not yet exactly there. I'll provide the correct query and a short commentary:
Basically, you're trying to join two subqueries. A subquery has to be enclosed in parenthesis (lines 2 and 4) and can be used wherever a table can be used. You can (or, in this case, need to) specify names for the subqueries, I've used A1 and B1.

The query joins the two subqueries on the ID column; if you omit the where clause, you'll get results for all IDs that appear in both tables.

Apart from not parenthesizing the inner queries, you've forgotten to provide a GROUP BY. We could do without the group by if you really ever need to select just one ID from both tables:
This is actually special case of a join, expressed in the old (Oracle's) syntax. Since we didn't specify any join condition, the result is a cartesian product of all the rows from both tables (or subqueries, in our case). Since both tables contain just one row, the result has one row too.
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Thanks a lot Martin Vajsar

I came with a solution :



That's pretty similar to what you did. Just Instead of "*", i named the columns
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Inner join as a sub Query