aspose file tools*
The moose likes JDBC and the fly likes LEFT JOIN problem. 2 tables, include null Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "LEFT JOIN problem. 2 tables, include null" Watch "LEFT JOIN problem. 2 tables, include null" New topic
Author

LEFT JOIN problem. 2 tables, include null

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Hi guys,
I believe this is a left join question (though I�m not 100% sure)
I have a table with the info below. Some data is not included in the second table but I still want the user to know about it. Can anyone tell what is the sql command for that?
Thanks


Table Target: ID, date, startDate, endDate, growth

Example:
101, 2006-01-01, 2006-12-31, 30
102 2006-01-01, 2006-12-31, 15

Table Production: ID, amount, date
101, 800, 2006-01-02
101, 900, 2006-02-03
101, 150, 2006-03-15
//no info on 102

I would like to get the following data:

ID, total_production
101, 1850, 30
102, null, 15
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
You're right it's an OUTER JOIN problem (left or right, depending on how you order your query). To complicate it, you've got a SUM across ID for one the tables. Not too hard, but challenging enough to be interesting.

I tend to use the Oracle non-standard syntax for these, which is:



I'm too brain-fried this evening to work out the standard syntax; if you need that, the above should give you a running start...
Naseem Khan
Ranch Hand

Joined: Apr 25, 2005
Posts: 809
You can go for this syntax....



Asking Smart Questions FAQ - How To Put Your Code In Code Tags
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Stu & Naseem,
thanks a lot, I played around using the basis you gave me and solved the problem.
thanks for the tip
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: LEFT JOIN problem. 2 tables, include null