SQL UNION returns duplicate results

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

Transact-SQL (2000)

SQL UNION returns duplicate results

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-13 : 08:28:53

Nemesis writes "Hi,
I have created an SQL view consisting of a UNION statement. The idea was to take results from a table, and depending on whether a particular field( called, IMEI) was present, it would take the results of that table with a value for IMEI or alternatively if IMEI was a blank field it would take the results of the table with IMEI as a blank. Now the problem is, there are 2 records in the view with exactly the same data, except IMEI has a value for 1 result and is blank for the other.

Here is the query:
SELECT InvoiceDate, Status, BranchID, [Value], Qty, PackageID, Package, SalesPersonID,
Case WHEN SalesPersonCommN IS NULL THEN 0 ELSE SalesPersonCommN END AS SalesPersonCommN,
Case WHEN SalesPersonCommU IS NULL THEN 0 ELSE SalesPersonCommU END AS SalesPersonCommU, IMEI, MSISDN
FROM
(SELECT dbo.InvoiceHeader.InvoiceDate, dbo.Contracts.Status, dbo.InvoiceHeader.BranchID,
SUM(dbo.InvoiceDetail.UnitPrice * dbo.InvoiceDetail.Qty) AS [Value], SUM(dbo.InvoiceDetail.Qty) AS [Qty],
Contracts.PackageID, dbo.Package.[Name] AS Package, dbo.Contracts.SalesPersonID, Package.SalesPersonCommN,
Package.SalesPersonCommU, Contracts.IMEI, Contracts.MSISDN
FROM dbo.InvoiceDetail INNER JOIN
dbo.InvoiceHeader ON dbo.InvoiceDetail.InvoiceID = dbo.InvoiceHeader.InvoiceID INNER JOIN
dbo.StockList ON dbo.InvoiceDetail.ProductCode = dbo.StockList.ProductCode INNER JOIN
dbo.Contracts ON dbo.InvoiceDetail.SerialNo = dbo.Contracts.IMEI INNER JOIN
dbo.Package ON dbo.Package.PackageID = dbo.Contracts.PackageID
WHERE (dbo.Contracts.Active = 1) AND (dbo.InvoiceDetail.SerialNo <> N'')
GROUP BY dbo.InvoiceHeader.InvoiceDate, dbo.Contracts.Status, dbo.InvoiceHeader.BranchID,
Contracts.PackageID, dbo.Package.[Name], dbo.Contracts.SalesPersonID, Package.SalesPersonCommN,
Package.SalesPersonCommU, Contracts.IMEI, Contracts.MSISDN
HAVING SUM(dbo.InvoiceDetail.Qty) <> 0
UNION
SELECT dbo.Contracts.ActivationDate, dbo.Contracts.Status, dbo.Accounts.BranchID, 0 AS Value, 1 as Qty, Contracts.PackageID,
dbo.Package.[Name] as Package, dbo.Contracts.SalesPersonID, Package.SalesPersonCommN, Package.SalesPersonCommU, '' AS IMEI, Contracts.MSISDN
FROM dbo.Accounts INNER JOIN dbo.Contracts ON dbo.Accounts.AccountID = dbo.Contracts.AccountID
INNER JOIN dbo.Package ON dbo.Package.PackageID = dbo.Contracts.PackageID
WHERE (dbo.Contracts.ContractTypeID IN (SELECT ContractTypeID FROM ContractType WHERE handsetrebate = 0)) AND (dbo.Contracts.Active = 1)) C

Thank you. I am using SQL Server 2000 on WinXP"

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-13 : 18:40:20

Yuk...
First of all, you might be better off posting the table design (DDL statements) for the required tables and then explain what it is you're trying to achieve.
Also, you will find things much easier if you use table aliases. At least your statements will be readable...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-14 : 05:06:41

also include sample input data (using INSERT statements ...to save us typing in your test data)....and sample matching expected results.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 13:27:15

quote:
Originally posted by AskSQLTeam

Nemesis writes "Hi,
I Now the problem is, there are 2 records in the view with exactly the same data, except IMEI has a value for 1 result and is blank for the other.

"Exactly the same data....except..."

I love that....

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-14 : 13:40:40

you really need to step back, take this problem one part at a time, and write your SQL piece by piece. It's always a bad sign when you are grouping by tons of columns like that in your SELECT list.

Focus on 1 calculation that you need to return -- i.e., focus on calculating SUM(Value) and SUM(Qty) per Invoice -- and take it 1 step at a time. then, in the end, when you have all of the calculations you need in a few seperate SQL statements, join them together.

Then, only as the very last step, add in joins to get data from related tables that is not involved in the primary calculations you need to return.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources