Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
AndyKing
Starting Member
3 Posts |
Posted - 2006-05-04 : 06:10:34
|
Hi, I have 2 tables:SALESPREVIOUSSALESCURRENTBoth tables hold information on week numbers, site number, sub department code, salesThe problem I have is that there is a sales code that is new and does not appear in the SALESPREVIOUS table but does in the SALESCURRENT table.When doing a simple join such asWhereSALESPREVIOUS.SITE = SALESCURRENT.SITEAND SALESPREVIOUS.WEEK = SALESCURRENT.WEEKAND SALESPREVIOUS.SUB_CODE = SALESCURRENT.SUB_CODEThe result does not bring back the sales for the missing sales code.I have fixed this by using a right join, however, will this cause problems if the problem is reversed ie a code that is valid for last year, but not for this year.What I need is a join that will populate the sales code whether it is valid for one year OR the other, not necessarily appearing in both.Your comments appreciated.ThanksAndy |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-04 : 06:12:36
|
use LEFT JOINselect *from SALESCURRENT c left join SALESPREVIOUS pon c.SITE = p.SITEand c.WEEK = p.WEEKand c.SUB_CODE = p.SUB_CODE KH |
|
|
AndyKing
Starting Member
3 Posts |
Posted - 2006-05-04 : 06:19:32
|
Hi, Thanks for your reply. This the result I had come to, but is the trouble with left and right joins that one of the tables is required?Would a Full Outer Join be what I was after??Many ThanksAndy |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-04 : 08:17:35
|
LEFT JOIN and RIGHT JOIN are basically the same.if you need - sales code that is new and does not appear in the SALESPREVIOUS table but does in the SALESCURRENT table- sales code that is new and does not appear in the SALESCURRENT table but does in the SALESPREVIOUS tablethen you need FULL OUTER JOIN KH |
|
|
AndyKing
Starting Member
3 Posts |
Posted - 2006-05-04 : 09:22:28
|
Great - thanks for your help.Andy |
|
|
|
|
|
|
|