La requête SQL UNION ne fonctionne pas

Voici mes requêtes actuelles:

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') 

Ceux-ci font exactement ce que je leur request, mais dès que j'essaie de les joindre en utilisant un «UNION» ou une «sous-requête», la deuxième requête se casse toujours et affiche les mauvaises informations.

Suis-je juste blond ne pas être en mesure de résoudre ce problème ou suis-je en train de faire quelque chose de mal.

Toute aide est appréciée.

Merci beaucoup Simon.

EDIT: Ce que je veux dire par "informations erronées" est que la 2ème requête renvoie toutes les valeurs plutôt que de suivre le préfixe CRMAF_ et de ne returnner que les valeurs du count sur lequel elle est exécutée.

Il me semble que vous devriez être en mesure d'get les mêmes résultats que vous le feriez à partir de la requête UNIONed, avec ce qui suit:

  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: ajout de la dernière condition OU entre parenthèses à la clause WHERE.

Il est difficile de deviner ce que vous entendez par "mauvaise information" mais je crois que vous voulez UNION ALL plutôt que UNION .

UNION supprime les duplicates afin que les loggings de la deuxième requête ne soient pas renvoyés s'ils ont été précédemment renvoyés par la première requête. De plus, les duplicates possibles dans une requête seront également éliminés.

Le nombre d'loggings dans UNION peut être inférieur au nombre total d'loggings dans deux requêtes.

Si vous souhaitez simplement concaténer deux jeux d'loggings, utilisez 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')