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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Join Question

Author  Topic 

AndyKing
Starting Member

3 Posts

Posted - 2006-05-04 : 06:10:34
Hi,
I have 2 tables:
SALESPREVIOUS
SALESCURRENT

Both tables hold information on week numbers, site number, sub department code, sales

The 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 as
Where
SALESPREVIOUS.SITE = SALESCURRENT.SITE
AND SALESPREVIOUS.WEEK = SALESCURRENT.WEEK
AND SALESPREVIOUS.SUB_CODE = SALESCURRENT.SUB_CODE

The 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.

Thanks

Andy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-04 : 06:12:36
use LEFT JOIN

select *
from SALESCURRENT c left join SALESPREVIOUS p
on c.SITE = p.SITE
and c.WEEK = p.WEEK
and c.SUB_CODE = p.SUB_CODE



KH

Go to Top of Page

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 Thanks

Andy
Go to Top of Page

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 table
then you need FULL OUTER JOIN



KH

Go to Top of Page

AndyKing
Starting Member

3 Posts

Posted - 2006-05-04 : 09:22:28
Great - thanks for your help.

Andy
Go to Top of Page
   

- Advertisement -