This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes SQL question: how to solve this query with 2 tables? (image included) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL question: how to solve this query with 2 tables? (image included)" Watch "SQL question: how to solve this query with 2 tables? (image included)" New topic
Author

SQL question: how to solve this query with 2 tables? (image included)

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Hi all,
In the table below you can see 2 tables; my goal is to SUM the production amount of every ID from Table_A BUT! if the ID has a branch_ID I should SUM their production as well.

For example, for ID 305 there is no branch so the sum will be 62 (30+32)

For ID 300 the sum should be 80+50 and to add 22 AND 23 (because 450 and 451 are branches of 300)

THANKS FOR ANY TIPS!!!



Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30068
    
149

Peter,
I like to think about more complex queries as simpler queries. That gets me to think about them from the inside out.

Do you know how to:
1) create a select returning the id in two columns - id and 'id for sum'
2) create a select returning the id and branch - branch and 'id for sum'
3) union the two and sum them up

I've seen you around enough to know you can do most or all of these. The main purpose of the simple questions is to show my thought process.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
ok Jeanna,

Thanks for the tip - I got the idea; I'll try that and get back with result (or questions :-) )

thank you!
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Solved!


so I followed your instructions and I got the answer


SELECT TMP.ID, SUM(production) AS TOTAL
FROM table_a AS A,

(
SELECT B.home_ID AS ID, B.branch_id AS ID_FOR_SUM
from table_b AS B

union

SELECT DISTINCT A.ID, A.ID AS ID_FOR_SUM
FROM table_a AS A
WHERE A.ID NOT IN

(
SELECT DISTINCT A.ID
FROM table_a AS A, table_b AS B
WHERE A.ID = B.branch_ID
)
) AS TMP

WHERE A.ID = TMP.ID_FOR_SUM

GROUP BY TMP.ID




Thanks Jeanne!
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30068
    
149

Good to hear you got it working. Thanks for sharing the final complex query. While you certainly know enough to translate my post into code, others seeing this thread in the future may not.
Gian Franco
blacksmith
Ranch Hand

Joined: Dec 16, 2003
Posts: 977
Hi Peter,

My fingers where itching to try another query

...what do think of the following query...

SELECT root.ID, root.total + NVL(branch.total, 0)
FROM (SELECT ID AS ID, SUM (production) AS total
FROM table_a
GROUP BY ID) root,
(SELECT home_id AS ID, SUM (production) AS total
FROM table_a, table_b
WHERE ID = branch_id
GROUP BY home_id) branch
WHERE root.ID = branch.ID(+)

Kind regards,

Gian


"Eppur si muove!"
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Hi Gian,

I'm using mySql so I can't use the function NVL.
Gian Franco
blacksmith
Ranch Hand

Joined: Dec 16, 2003
Posts: 977
Peter Primrose wrote:
I'm using mySql so I can't use the function NVL.


Ok...anyway you could use ifNULL() if I'm not mistaken...
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Use IFNULL in mysql.
edit: ah. I'm second!


OCUP UML fundamental and ITIL foundation
youtube channel
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL question: how to solve this query with 2 tables? (image included)
 
Similar Threads
Hibernate JPQL/HQL: aggregate functions show results of wrong table/entity joined twice
reference ... is ambiguous. How to handle it?
Backup MySQL
how to update only the modified field values
LEFT JOIN problem. 2 tables, include null