• 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
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

Inner join as a sub Query

 
Kunal Lakhani
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?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Netbeans IDE Oracle Chrome
  • 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
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!