Urgent, pls help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Urgent, pls help

Author

Topic

josh777
Starting Member

12 Posts

Posted - 2007-05-31 : 21:36:50

I am working on a report that needs to be submitted.

I link multiple tables using joins

I narrowed down the problem

Basically this is my problem

tblSales
R#(salesno) ItemCode
R1 A1
R1 A2
R3 A4
R4 A6
R7 A7

tblPurchase(linked to Sales)
R# P#(purchaseno) ItemCode
R1 P1 A1
R1 P2 A2
R4 P5 A6
R3 P3 A4
null P6 A8

the problem i face when i link is

R#(salesno) ItemCode P#
R1 A1 P1
R1 A2 P1
R1 A1 P2
R1 A2 P2

this links twice

I want to link both these tables to show all possible situations to show in a report.
The result should be something like this

R#(salesno) ItemCode P#
R1 A1 P1
R1 A2 P2
R3 A4 P3
R4 A6 P5
R7 A7 -
- A8 P6

Thanks a lot,
josh

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-05-31 : 23:30:55

This is illustrated in a confusing way, but I think you want a link on both fields using a outer join


Select coalesce(a.[R#],b.[R#]) as [R#],coalesce(a.[ItemCode],b.[ItemCode])as [ItemCode],coalesce(a.[P#],b.[P#]) as [P#]
from              tblSales a
Full Outer join   tblPurchase b
on                a.[r#] = b.[r#]
and               a.[ItemCode] = b.[ItemCode]

josh777
Starting Member

12 Posts

Posted - 2007-06-01 : 14:00:08

Hi Vinnie,
Thanx for your response. The thing is
if i link both fields i am not able to see sales order that do not have a purchase order and using coalesce brings records that i dont need. ( though it does

josh777
Starting Member

12 Posts

Posted - 2007-06-01 : 14:01:18

I figured out ,i did try using two links , it solves the problem ! thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources