asked on

Why is my SQL Query eliminating results

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

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joe Grosskopf

ASKER

GENIUS! Thanks. I never knew that....only in programming could a 3 letter word create such havoc.

Jim Horn

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