Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
>hi
>
>I have two tables
>
>Table1 has : NumDoc , TotDoc , TipoDoc
>Table 2 has : NumDoc , ValReg , TipoDoc
>
>I want select all in Table1 that exist in Table 2 where ValReg (in Table2 ) < TotDoc ( in Table1 ) . I want more ,
>i want all in Table1 that not exist in Table2 . Table2 can have more than one , with same NumDoc and TipDoc , so i want
>sum ValReg for same NumDoc and TipDoc .
I think what you're looking for is:
SELECT Table1.NumDoc, Table1.TipoDoc, ;
Table1.TotDoc - SUM(Table2.ValReg) ;
FROM Table1 ;
JOIN Table2 ;
ON Table1.NumDoc = Table2.NumDoc ;
GROUP BY 1, 2 ;
UNION ;
SELECT NumDoc, TipoDoc, TotDoc ;
FROM Table1 ;
WHERE NumDoc NOT IN (SELECT NumDoc FROM Table2) ;
INTO CURSOR Result
I'm not totally sure this will work. I've never tried using an aggregate function on only part of an expression like that. Also, you need to make sure that the size of the numeric field matches in the two queries.
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