troubleshooting Question

Duplicate Values

Avatar of Clif
ClifFlag for United States of America asked on
Microsoft SQL Server
9 Comments2 Solutions323 ViewsLast Modified:
I have two tables similar to this:
-Invoice_Table-
Customer  Job_No  Sale_Amt
12345678  123456  123.45
87654321  246800  645.23

-Vendor_Table-
Customer  Job_No  Vendor  Vendor_Cost
12345678  123456  ABCDEF  10.50
12345678  123456  UVWXYZ  20.92
87654321  246800  LMNOPQ  12.45

Open in new window

And I have a query similar to the following (but a LOT more complex):
SELECT
	Inv.Customer,
	Inv.Job_No,
	Ven.Vendor,
	Ven.Cendor_Cost,
	Inv.Sale_Amt
FROM
	Invoice_Table Inv
	LEFT OUTER JOIN Vendor_Table Ven
		ON Inv.Customer = Ven.Customer
		AND Inv.Job_No = Ven.Job_No

Open in new window

This query produces output similar to the following:
Customer  Job_No  Vendor  Vendor_Cost  Sale_Amt
12345678  123456  ABCDEF  10.50        123.45
12345678  123456  UVWXYZ  20.92        123.45
87654321  246800  LMNOPQ  12.45        645.23

Open in new window


This is causing a problem in Crystal (where I use this) as it's adding 123.45 twice.

Is there any way that I can, using the tables provided (that I can't modify) produce results something like this:
Customer  Job_No  Vendor  Vendor_Cost  Sale_Amt
12345678  123456  ABCDEF  10.50        123.45
12345678  123456  UVWXYZ  20.92        0 (or NULL)
87654321  246800  LMNOPQ  12.45        645.23

Open in new window


TIA
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros