SQL UNION query not working

Question

here is my current queries:

1

SELECT FilteredInvoice.accountidname, 
       FilteredInvoice.createdon, 
       FilteredInvoice.createdon AS sort_date, 
       FilteredInvoice.duedate, 
       FilteredInvoice.invoicenumber, 
       FilteredInvoice.statecodename, 
       FilteredInvoice.totalamount_base, 
       CONVERT(datetime, NULL) AS mag_paymentdate, 
       0 AS mag_amount_base, 
       GETDATE() AS Today
  FROM FilteredAccount AS CRMAF_FilteredAccount 
  JOIN FilteredInvoice ON FilteredInvoice.accountid = CRMAF_FilteredAccount.accountid 
  JOIN FilteredMag_Payment ON FilteredInvoice.invoiceid = FilteredMag_Payment.mag_invoiceid
 WHERE (FilteredInvoice.statecodename <> 'Canceled')

2

   SELECT FilteredInvoice_1.accountidname, 
          FilteredInvoice_1.createdon, 
          FilteredInvoice_1.createdon AS sort_date, 
          FilteredInvoice_1.duedate, 
          FilteredInvoice_1.invoicenumber, 
          FilteredInvoice_1.statecodename, 
          FilteredInvoice_1.totalamount_base, 
          FilteredMag_Payment.mag_paymentdate, 
          FilteredMag_Payment.mag_amount_base, 
          GETDATE() AS Today
     FROM FilteredAccount AS CRMAF_FilteredAccount 
LEFT JOIN FilteredInvoice AS FilteredInvoice_1 ON FilteredInvoice_1.accountid = CRMAF_FilteredAccount.accountid 
     JOIN FilteredMag_Payment ON FilteredInvoice_1.invoiceid = FilteredMag_Payment.mag_invoiceid
    WHERE (FilteredInvoice_1.statecodename <> 'Canceled')

These alone do exactly what i am wanting them to but as soon as i try and join them using a "UNION" or "Sub-query" the second query always breaks and displays the wrong information.

Am I just being blond not being able to work this out or am I actually doing something wrong.

All help is appreciated.

Many thanks Simon.

EDIT: What I mean by "Wrong information" is that the 2nd query is returning all values rather then following the CRMAF_ prefix and returning only values from the account it is run on.

Answer 1

It's hard to guess what do you mean by "wrong information" but I believe you want UNION ALL rather than UNION.

UNION removes duplicates so the records from the second query won't be returned if they were previously returned by the first query. In addition, the possible duplicates within one query will be eliminated too.

The number of records in a UNION can be less than the total count of records in two queries.

If you just want to concatenate two recordsets, use UNION ALL:

SELECT FilteredInvoice.accountidname, 
       FilteredInvoice.createdon, 
       FilteredInvoice.createdon AS sort_date, 
       FilteredInvoice.duedate, 
       FilteredInvoice.invoicenumber, 
       FilteredInvoice.statecodename, 
       FilteredInvoice.totalamount_base, 
       CONVERT(datetime, NULL) AS mag_paymentdate, 
       0 AS mag_amount_base, 
       GETDATE() AS Today
  FROM FilteredAccount AS CRMAF_FilteredAccount 
  JOIN FilteredInvoice ON FilteredInvoice.accountid = CRMAF_FilteredAccount.accountid 
  JOIN FilteredMag_Payment ON FilteredInvoice.invoiceid = FilteredMag_Payment.mag_invoiceid
 WHERE (FilteredInvoice.statecodename <> 'Canceled')
UNION ALL
   SELECT FilteredInvoice_1.accountidname, 
          FilteredInvoice_1.createdon, 
          FilteredInvoice_1.createdon AS sort_date, 
          FilteredInvoice_1.duedate, 
          FilteredInvoice_1.invoicenumber, 
          FilteredInvoice_1.statecodename, 
          FilteredInvoice_1.totalamount_base, 
          FilteredMag_Payment.mag_paymentdate, 
          FilteredMag_Payment.mag_amount_base, 
          GETDATE() AS Today
     FROM FilteredAccount AS CRMAF_FilteredAccount 
LEFT JOIN FilteredInvoice AS FilteredInvoice_1 ON FilteredInvoice_1.accountid = CRMAF_FilteredAccount.accountid 
     JOIN FilteredMag_Payment ON FilteredInvoice_1.invoiceid = FilteredMag_Payment.mag_invoiceid
    WHERE (FilteredInvoice_1.statecodename <> 'Canceled')

Answer 2

It looks to me as though you should be able to get the same results as you would from the UNIONed query, with the following:

   SELECT FilteredInvoice_1.accountidname, 
          FilteredInvoice_1.createdon, 
          FilteredInvoice_1.createdon AS sort_date, 
          FilteredInvoice_1.duedate, 
          FilteredInvoice_1.invoicenumber, 
          FilteredInvoice_1.statecodename, 
          FilteredInvoice_1.totalamount_base, 
          CASE PF.pay_flag 
              WHEN 0.0 THEN CONVERT(datetime, NULL)
              ELSE FilteredMag_Payment.mag_paymentdate
          END AS mag_paymentdate, 
          FilteredMag_Payment.mag_amount_base * PF.pay_flag AS mag_amount_base, 
          GETDATE() AS Today
     FROM FilteredAccount AS CRMAF_FilteredAccount 
     CROSS JOIN (SELECT 1.0 pay_flag UNION SELECT 0.0) AS PF
     JOIN FilteredInvoice AS FilteredInvoice_1 ON FilteredInvoice_1.accountid = CRMAF_FilteredAccount.accountid 
     LEFT JOIN FilteredMag_Payment ON FilteredInvoice_1.invoiceid = FilteredMag_Payment.mag_invoiceid
    WHERE (FilteredInvoice_1.statecodename <> 'Canceled') AND
          (PF.pay_flag = 0 OR FilteredMag_Payment.mag_invoiceid IS NOT NULL)

EDIT: LEFT JOIN FilteredMag_Payment

FURTHER EDIT: added final parenthesised OR condition to WHERE clause.