Complexe SQL Query

Question

I have 3 tables:

Table Invoices(Invoice, InvoiceAmount(float), Other infos ...), table Payments(Payment, PaymentAmount(float), Other infos ...) and table PaymentsDet(Id, Invoice, Payment, Amount(float)). The table PaymentsDet link an invoice and a payment with an amount (the portion of the invoice paid by that payment).

I need a query that returns informations about each invoice +

IF (there is exactly 1 payement for that invoice)

Payment,SUM(PayementsDet.Amount), Other Payment infos ...

ELSE (More than 1 payement or no payement at all)

Count(Payment), SUM(PayementsDet.Amount), Complete other Payment infos with NULL values or ''.

Thank you for your time, and hope there is someone smart enough that can help me on this.

EDIT :

    SELECT        Factures.Facture, Factures.Client, Factures.DateFacture, Factures.MoisFacture, Factures.DateRéception, Factures.Echéance, Factures.Montant, Factures.TxTVA, 
                         Factures.Activité,
                             (SELECT        CASE WHEN SUM(Montant) IS NULL THEN '0' ELSE SUM(Montant) END AS Expr1
                               FROM            RèglementsDet
                               WHERE        (Facture = Factures.Facture) AND (Validé = 1)) AS MontantRegl,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements.Règlement) > '1' THEN COUNT(DISTINCT Règlements.Règlement) 
                                                         WHEN COUNT(DISTINCT Règlements.Règlement) = '1' THEN
                                                             (SELECT        MIN(Règlements.Règlement) AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) END AS Règlement
                               FROM            Règlements INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS Règlement
FROM            Factures LEFT OUTER JOIN
                         RèglementsDet AS RèglementsDet_1 ON Factures.Facture = RèglementsDet_1.Facture
GROUP BY Factures.Facture, Factures.Client, Factures.DateFacture, Factures.MoisFacture, Factures.DateRéception, Factures.Echéance, Factures.Montant, Factures.TxTVA, 
                         Factures.Activité

I think I figured it out, if anyone got a better (more readable) query.

Answer 1

Thank you Fares for your time and effort.

The problem in my case wasn't when there is 1 or 0 payment, but the real problem was when I got multiple payments for the same invoice. At last I figured out I way to do it even if it is kinda complexe, but here is the solution I found hope it will help someone else out there.

SELECT        Factures.Facture, Factures.Client AS [Code C/F], Factures.DateFacture, Factures.MoisFacture, Factures.DateRéception, Factures.Echéance, Factures.Montant, 
                         Factures.TxTVA, Factures.Activité,
                             (SELECT        CASE WHEN SUM(Montant) IS NULL THEN '0' ELSE SUM(Montant) END AS Expr1
                               FROM            RèglementsDet
                               WHERE        (Facture = Factures.Facture) AND (Validé = 1)) AS MontantRegl,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements.Règlement) > '1' THEN COUNT(DISTINCT Règlements.Règlement) 
                                                         WHEN COUNT(DISTINCT Règlements.Règlement) = '1' THEN
                                                             (SELECT        MIN(Règlements.Règlement) AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) END AS Règlement
                               FROM            Règlements INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS Règlement,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements_1.Règlement) = '1' THEN
                                                             (SELECT        TOP (1) Règlements.Banque AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) ELSE '' END AS Expr1
                               FROM            Règlements AS Règlements_1 INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements_1.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS Banque,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements_1.Règlement) = '1' THEN
                                                             (SELECT        TOP (1) Règlements.ModeDeRèglement AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) ELSE '' END AS Expr1
                               FROM            Règlements AS Règlements_1 INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements_1.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS ModeRegl,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements_1.Règlement) = '1' THEN
                                                             (SELECT        TOP (1) Règlements.NumDocument AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) ELSE '' END AS Expr1
                               FROM            Règlements AS Règlements_1 INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements_1.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS NumDocument,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements_1.Règlement) = '1' THEN
                                                             (SELECT        TOP (1) Règlements.DateRèglement AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) ELSE NULL END AS Expr1
                               FROM            Règlements AS Règlements_1 INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements_1.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS DateRèglement, Factures.Montant -
                             (SELECT        CASE WHEN SUM(Montant) IS NULL THEN '0' ELSE SUM(Montant) END AS Expr1
                               FROM            RèglementsDet AS RèglementsDet_3
                               WHERE        (Facture = Factures.Facture) AND (Validé = 1)) AS Solde
FROM            Factures LEFT OUTER JOIN
                         RèglementsDet AS RèglementsDet_1 ON Factures.Facture = RèglementsDet_1.Facture
GROUP BY Factures.Facture, Factures.Client, Factures.DateFacture, Factures.MoisFacture, Factures.DateRéception, Factures.Echéance, Factures.Montant, Factures.TxTVA, 
                         Factures.Activité

P.S : Facture = Invoice and Règlement = Payment

Answer 2

Because, you're using CASE statement, i guess you should understand this SQL query easily and adjust names to your fields names:

(SELECT P.Payement,SUM(D.Amount), P.CreatedAt, P.Expired FROM Invoices I, Payements P, PayementsDet D WHERE 1 = (
    SELECT(
             CASE 
                  WHEN (SELECT count(D.Payement) FROM Invoices I, Payements P WHERE ( D.Invoice = I.Invoice AND P.Payement = D.Payement ) GROUP BY I.Invoice) = 1 
                     THEN 1 
                  ELSE 0 
             END)
    FROM  Invoices I, Payements P, PayementsDet D
))
UNION
(SELECT P.Payement,SUM(D.Amount), null, null FROM Invoices I, Payements P, PayementsDet D WHERE 0 = (
    SELECT(
             CASE 
                  WHEN (SELECT count(D.Payement) FROM Invoices I, Payements P WHERE ( D.Invoice = I.Invoice AND P.Payement = D.Payement ) GROUP BY I.Invoice) = 1 
                     THEN 1 
                  ELSE 0 
             END)
    FROM  Invoices I, Payements P, PayementsDet D
));