mySQL Left Join sur plusieurs tables avec des conditions

J'ai un problème. J'ai 4 tables:

Invoice_Payment, Facture, Client, et le Calendrier

En gros, j'ai le dessous de la requête, qui fonctionne bien, à l'exception, des mois sans date_due ne reviennent pas. I. E mois seulement avec un date_due sera retourné.

Remarque: le calendrier de table tout simplement des listes de tous les jours de l'année. Il contient un seul col appel date_field

Base de données: http://oberto.co.nz/jInvoice.txt

Résultat attendu: ma requête actuelle, ci-dessous, sera de retour quelque chose comme:

month       total
August      5
September   0
October  196
November  205
December  214
January  229

Avis combien de septembre n'est pas retourné? C'est parce que le tableau Invoice_Payment n'a pas de date_due enregistrement

Je pense que je dois utiliser une jointure gauche et rejoindre le calendrier de table avec quelque chose comme:
LEFT JOIN Calendrier SUR Invoice_Payments.date_paid = Calendrier.date_field. Mais je vais avoir pas de chance

SELECT MONTHNAME(Invoice_Payments.date_paid) as month, SUM(Invoice_Payments.paid_amount) AS total
FROM Invoice, Client, Invoice_Payments
WHERE Client.registered_id = 1
AND Client.id = Invoice.client_id
And Invoice.id = Invoice_Payments.invoice_id
AND date_paid IS NOT NULL
GROUP BY YEAR(Invoice_Payments.date_paid), MONTH(Invoice_Payments.date_paid)

Toute aide appréciée.

date_due est dans la table de facture ou invoice_payment table??
Je ne vois pas de n'importe où dans la requête de filtrage date_due. vous êtes sûr que c'est pas le retour de ces lignes?
Pourquoi serait - gauche rejoindre la calendar tableau de l'aide?
date_due est dans le invoice_payment table
J'ai pensé à gauche rejoindre le calendrier de table pour montrer tous les mois, même ceux sans une date de paiement

InformationsquelleAutor Jarrod | 2011-01-16

4

Il semble que vous essayez de trouver des valeurs pour toutes les dates à l'intérieur d'une plage, indépendamment de savoir si il existe une valeur ou pas. En supposant que nous avons un Calendrier de table structuré comme suit:
```
Create Table Calendar
(
    [Date] not null Primary Key
)
```
Votre requête pourrait ressembler à de la sorte (où X et Y représentent le début et date de fin de la plage dans laquelle vous étudiez):
```
Select Year(C.Date), MonthName(C.Date) As Month
    , Coalesce(Sum(IP.paid_amount),0) As Total
From Calendar As C
    Left Join (Invoice As I
        Join Client As C1
            On C1.id = I.client_id
                And C.registered_id = 1
        Join Invoice_Payments As IP
            On IP.Invoice_id = I.Id)
        On IP.date_paid = C.date
Where C.Date Between X and Y
Group By Year(C.Date), MonthName(C.Date)
```
Techniquement, la requête ci-dessus devrait faire l'affaire. Cependant, une autre alternative est l'utilisation d'un dérivé de la table sur laquelle vous m'avez demandé dans les commentaires:
```
Select Year(C.Date), MonthName(C.Date) As Month
    , Coalesce(Sum(Z.paid_amount),0) As Total
From Calendar As C
    Left Join   (
                Select IP.date_paid, IP.paid_amount
                From Invoice As I
                        Join Client As C1
                            On C1.id = I.client_id
                                And C.registered_id = 1
                        Join Invoice_Payments As IP
                            On IP.Invoice_id = I.Id
                ) As Z
        On Z.date_paid = C.date
Where C.Date Between X and Y
Group By Year(C.Date), MonthName(C.Date)
```
- Bonjour Thomas. Merci pour la réponse. J'ai "#1066 - Pas de table unique/alias: 'C'"
- Fixe. J'ai utilisé le alias "C" pour le Calendrier de table et de la table des Clients. Ont simplement changé de l'alias de la table des Clients.
- Salut Thomas, merci pour le montage. Cependant, j'ai toujours quelques erreurs que je ne peut pas résoudre: "Unknown column 'I. date_paid" dans "sur la clause". J'ai ajouté le schéma suivant pour vous aider à comprendre mon problème: oberto.co.nz/db-sql.png
- C'est une colonne de la requête initiale. Puisque vous n'avez pas utiliser un préfixe, je n'ai aucun moyen de savoir dans quelle table elle est contenue. J'ai deviné que c'était la Facture de la table. Cependant, si c'est le Invoice_Payment table il suffit de changer I. date_paid à la propriété intellectuelle.date_paid.
- Merci encore. Toujours pas le résultat que je suis après, Seulement en janvier, les données sont incluses. Voir oberto.co.nz/Selection_204.png. Je pense qu'il est sur la bonne voie, mais il est également absente de la condition du Client.registered_id = 1. Cette conduite est moi mentale
- Étant donné que vous êtes de gauche de se joindre à l'Agenda de la table, il ya seulement tellement de nombreuses raisons que vous obtenez seulement des données de janvier. 1. Votre X et Y uniquement inclus Janv. 2. Vous mettez le registered_id=1 critères dans la clause where et uniquement Jan de données qui a de la valeur. 3. Votre calendrier de table ne comprend Jan et pas toutes les dates à travers la plage que vous souhaitez.
- Btw, j'ai ajouté le registered_id critères à ma solution.
- Merci encore. Nous nous rapprochons, je peux le sentir! J'ai changé la ligne en ajoutant MONTHNAME maintenant: MONTHNAME(PI.date_paid) = MONTHNAME(C. date_field). Cela a aidé. J'ai obtenu un résultat correct pour chaque mois. Le problème maintenant est que les résultats sont trop élevés. Une idée? J'apprécie VRAIMENT votre temps!
- Tout d'abord, je n'ai pas remarqué que les colonnes que vous avez utilisé dans le Groupe ont été différentes dans le Sélectionner. J'ai corrigé ça. Le rejoindre sur le mois du Calendrier n'est probablement pas correcte. Vous devriez être en mesure de joindre directement sur le Calendrier.La Date que j'ai en supposant que le Calendrier de table, en effet, contient toutes les dates à travers la plage que vous souhaitez.
- Btw, il pourrait être utile si vous avez modifié votre message original afin d'y inclure certains mêmes données d'entrée qui exprime le bord des cas et certains résultats attendus.
- J'ai ajouté de la base de données et les résultats attendus.
- Une table dérivée doit fonctionnera également. J'ai mis à jour mon post pour montrer un exemple.
- Merci Thomas. Le dernier a travaillé un régal. Merci pour votre temps!
- Logiquement, ils sont identiques. Si le premier ne fonctionne pas, cela signifie que MySQL n'honore pas les jointures entre parenthèses.
InformationsquelleAutor Thomas
1

J'ai couru dans un problème similaire et résolus en déplaçant le non calendrier de tableau des conditions de la clause where et dans la jointure. Voir mon explication complète ici: https://gist.github.com/1137089

Dans votre cas, vous aurez besoin de prendre le from table1, table2, tableau3 dans table1 left join sur x=y, etc...

InformationsquelleAutor todd
0

Je voudrais définir explicitement tous vos jointures pour des raisons de lisibilité.

Un exemple simple d'une jointure externe la préservation de toutes les lignes de la première table, remplir toutes les colonnes manquantes dans le deuxième tableau avec les valeurs null:
```
select column1,
from table1
left outer join table2
  on table1.key = table2.key
```
En supposant que le Calendrier de table est l'un provoque la perte de données, voici un coup à votre requête:
```
SELECT MONTHNAME(Invoice_Payments.date_paid) as month,
       SUM(Invoice_Payments.paid_amount)
FROM Invoice
JOIN Client
  ON Client.id = Invoice.client_id
 AND Client.registered_id = 1
JOIN Invoice_Payments
  ON Invoice_Payments.invoice_id = Invoice.id
LEFT OUTER JOIN Calendar
  ON Calendar.key = #yourothertable#.key    
WHERE date_paid IS NOT NULL
GROUP BY YEAR(Invoice_Payments.date_paid), MONTH(Invoice_Payments.date_paid)
```
Bonne chance!
- Salut Cag. Merci pour la réponse. Je pense que c'est sur la bonne voie. Mais c'est la Invoice_Payment table, me faisant perdre des données. J'ai mis à jour ma question ci-dessus montrent. Si il n'y a pas d'enregistrement dans la Invoice_Payment table pour un mois en particulier, alors que le mois de ne pas s'afficher. Et c'est là que je suis bloqué
- Hey Jarrod, vous pourriez avoir pensé à elle, mais si vous êtes de perdre des données en raison de la Invoice_Payment table, alors vous pouvez changer ce joindre à la déclaration que j'ai écrit à: "JOINTURE EXTERNE GAUCHE Invoice_Payments" et de supprimer les deux lignes sur le calendrier
InformationsquelleAutor cag

Vous devez vous connecter pour publier un commentaire.