This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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.
Joined: Jun 05, 2010
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