We help IT Professionals succeed at work.
Get Started

Duplicate Values

Clif
Clif asked
on
322 Views
Last Modified: 2012-06-22
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
Comment
Watch Question
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This problem has been solved!
Unlock 2 Answers and 9 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE