[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Why is my SQL Query eliminating results

Posted on 2014-08-04
3
Medium Priority
?
328 Views
Last Modified: 2014-08-04
I am running a union query and it is eliminating results. If I run the query:

SELECT        c.ActDate AS inp_date, c.BegDate, c.ActSales, c.ActShip, b.order_no, COALESCE (b.line_type, 'order') AS line_type, COALESCE (b.tl_amount, 0) AS tl_amount, b.item, a.WorkDaySm, a.Week, d.SalesBud, a.NoDays,
                          d.ShipBud
FROM            backlogdates AS c LEFT OUTER JOIN
                         sp_Daily_Orders AS b ON c.ActDate = b.inp_date LEFT OUTER JOIN
                         WorkDayWeek AS a ON c.ActDate = a.Date LEFT OUTER JOIN
                         spbudget AS d ON c.BegDate = d.BegDate
where b.order_no = '170608'

my result set is:

inp_date      BegDate      ActSales      ActShip      order_no      line_type      tl_amount      item      WorkDaySm      Week      SalesBud      NoDays      ShipBud
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL

which is correct, but once I union the deliveries, I lose all but three.

SELECT        c.ActDate AS inp_date, c.BegDate, c.ActSales, c.ActShip, b.order_no, COALESCE (b.line_type, 'order') AS line_type, COALESCE (b.tl_amount, 0) AS tl_amount, b.item, a.WorkDaySm, a.Week, d.SalesBud, a.NoDays,
                          d.ShipBud
FROM            backlogdates AS c LEFT OUTER JOIN
                         sp_Daily_Orders AS b ON c.ActDate = b.inp_date LEFT OUTER JOIN
                         WorkDayWeek AS a ON c.ActDate = a.Date LEFT OUTER JOIN
                         spbudget AS d ON c.BegDate = d.BegDate
where b.order_no = '170608'
UNION
SELECT        c.ActDate AS inp_date, c.BegDate, c.ActSales, c.ActShip, b.order_no, COALESCE (b.line_type, 'delivery') AS line_type, COALESCE (b.tl_amount, 0) AS tl_amount, b.item, a.WorkDaySm, a.Week, d.SalesBud,
                         a.NoDays, d.ShipBud
FROM            backlogdates AS c LEFT OUTER JOIN
                         sp_Daily_Deliveries AS b ON c.ActDate = b.inp_date LEFT OUTER JOIN
                         WorkDayWeek AS a ON c.ActDate = a.Date LEFT OUTER JOIN
                         spbudget AS d ON c.BegDate = d.BegDate
where b.order_no = '170608'

inp_date      BegDate      ActSales      ActShip      order_no      line_type      tl_amount      item      WorkDaySm      Week      SalesBud      NoDays      ShipBud
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL

I cannot figure out why the query is only showing 3
0
Comment
Question by:jsgrosskopf
  • 2
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40239160
UNION eliminates duplicates, and looking at your sample data above it looks like there's three unique rows, and a wompload of duplicates.

UNION ALL does not eliminate duplicates.
0
 

Author Closing Comment

by:jsgrosskopf
ID: 40239243
GENIUS! Thanks. I never knew that....only in programming could a 3 letter word create such havoc.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40239428
Yep.

UNION ALL is used either when you need every row (your case), or the two sets are distinct enough that you don't have to worry about it (SELECT name FROM burger_joints UNION ALL SELECT name FROM whorehouses).  

UNION eliminates duplicates, so it takes longer to run.

Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question