This week's giveaway is in the JDBC forum.
We're giving away four copies of Java Database Connections & Transactions (e-book only) and have Marco Behler on-line!
See this thread for details.
Win a copy of Java Database Connections & Transactions (e-book only) this week in the JDBC forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

Inner join as a sub Query  RSS feed

 
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 622
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
All of the world's problems can be solved in a garden - Geoff Lawton. Tiny ad:
how do I do my own kindle-like thing - without amazon
https://coderanch.com/t/711421/engineering/kindle-amazon
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!