Requête MySQL - SOMME de COUNT à partir de plusieurs tables

Question

J'ai trois tableaux :

• clients : id, nom
• contrats_bijoux : id, customer_id, paid, transferred, final_date
• contrats_objets : id, customer_id, paid, transferred, final_date

Comme vous le voyez, la structure des deux derniers tableaux est la même. Le tableau " payé "et le " transféré Les champs "0" et "1" contiennent la valeur 0 ou 1.

Ce dont j'ai besoin, c'est d'une requête qui renvoie tous les clients (qu'ils aient ou non des contrats), et pour chaque client : id, name, count_contracts_all , count_contracts_active

où :

• count_contracts_all signifierait la somme de [SELECT COUNT( * ) FROM contracts_jewels WHERE customer_id=3 (par exemple)] et [SELECT COUNT( * ) FROM contracts_objects WHERE customer_id=3 (par exemple)]
• count_contracts_active signifierait la somme de [SELECT COUNT( * ) FROM contracts_jewels WHERE customer_id=3 AND final_date>=Now() AND paid=0 AND transferred=0] et [SELECT COUNT( * ) FROM contracts_objects WHERE customer_id=3 AND final_date>=Now() AND paid=0 AND transferred=0]

Une idée ? Pourriez-vous m'aider ? Je vous remercie de votre attention.

Answer 1

Vous pouvez compter les contrats séparément et les rattacher aux clients :

SELECT
    c.id,
    COALESCE(oc.active_count,0) + COALESCE(jc.active_count,0) as count_contracts_active,
    COALESCE(oc.total_count,0) + COALESCE(jc.total_count,0) as count_contracts_all
FROM customers c
LEFT JOIN (
    SELECT
        customer_id
        COUNT(*) as total_count,
        COUNT(IF(final_date>=Now() AND paid=0 AND transferred=0,1,NULL)) as active_count
    FROM contracts_jewels
    GROUP BY customer_id
) as oc ON oc.customer_id = c.id
LEFT JOIN (
    SELECT
        customer_id
        COUNT(*) as total_count,
        COUNT(IF(final_date>=Now() AND paid=0 AND transferred=0,1,NULL)) as active_count
    FROM contracts_objects
    GROUP BY customer_id
) as jc ON jc.customer_id = c.id

Answer 2

La solution la plus rapide à laquelle je pense actuellement est la suivante :

SELECT COUNT(`temp_table`.*) FROM (
    SELECT * FROM contracts_jewels WHERE customer_id=3 UNION ALL
    SELECT * FROM contracts_objects WHERE customer_id=3) AS `temp_table`

ET

SELECT COUNT(`temp_table`.*) FROM (
SELECT * FROM contracts_jewels WHERE customer_id=3 AND final_date>=Now() AND paid=0 AND transferred=0 UNION ALL
    SELECT * FROM contracts_objects WHERE customer_id=3 AND final_date>=Now() AND paid=0 AND transferred=0)  AS `temp_table`

Answer 3

Vous pouvez joindre deux fois chacune de ces tables et ajouter les tableaux correspondants. COUNT dans votre résultat :

SELECT
    c.id,
    (COUNT(cj1.id)+COUNT(co1.id)) AS count_contracts_all,
    (COUNT(cj2.id)+COUNT(co2.id)) AS count_contracts_active
FROM
    customers c
    LEFT OUTER JOIN contracts_jewels cj1 ON c.id = cj1.customer_id
    LEFT OUTER JOIN contracts_objects co1 ON c.id = co1.customer_id
    LEFT OUTER JOIN contracts_jewels cj2 ON
        c.id = cj2.id AND
        cj2.final_date >= NOW() AND
        cj2.paid = 0 AND
        cj2.transferred = 0
    LEFT OUTER JOIN contracts_object co2 ON
        c.id = co2.id AND
        co2.final_date >= NOW() AND
        co2.paid = 0 AND
        co2.transferred = 0
GROUP BY c.id

Note : Je n'ai pas réalisé ce projet, mais j'espère qu'il vous mettra sur la bonne voie.