Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
> My query uses two tables, a parent and a child. For all rows in the parent table, the query must sum the related rows in the child table and subtract the sum from a column in the parent table. (I'm trying to abstract it so we don't have to into all the details of the database design):
>
> The query looks something like this: Select ParentTable.Amount - (SELECT SUM(amount) FROM ChildTable WHERE ChildTable.ForeignKey = ParentTable.PrimaryKey) AS AmountLeftOver FROM ParentTable
>
Try something like this:
SELECT ParentTable.PrimaryKey, ParentTable.Amount - SUM(ChildTable.Amount) ;
FROM ParentTable ;
JOIN ChildTable ;
ON ParentTable.PrimaryKey = ChildTable.ForeignKey ;
GROUP BY 1
I'm not totally sure the second expression will work. If it doesn't, break that into two fields in the field list: ParentTable.Amount, SUM(ChildTable.Amount). Then you can do the calculation afterward, either in Xbase code or with a second (very simple) query.
Tamar
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement