Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

LEFT JOIN problem. 2 tables, include null

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 809
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can go for this syntax....

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Stu & Naseem,
thanks a lot, I played around using the basis you gave me and solved the problem.
thanks for the tip
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic