Hard question about SQL & DB

[Shurikn]

ok here's my request for now:

SELECT tblUser.Nom, tblUser.Prenom, Sum(CréditsDeVente) as somme, tblUser.Societe, tblUser2.Nom, tblUser2.Prenom 
FROM tblUser, tblIndividus, tblProjet, tblUser as tblUser2 
WHERE tblProjet.ProjetIndividu=tblIndividus.NomPrénom 
AND tblIndividus.IndividuChargéDeCompte=tblUser2.index 
AND tblIndividus.IndividuRessAffairesEst=tblUser.index 
AND Statut="Terminé - succès" 
GROUP BY tblUser.Nom, tblUser.Prenom, tblUser.Societe, tblUser2.Nom, tblUser2.Prenom 
ORDER BY Sum(CréditsDeVente) DESC, tblUser.Societe, tblUser.Nom, tblUser.Prenom

 

but there is juste a problem, I want the data from the Individus who dont have a ChargéDeCompte in other words, all the data from wich this part: "AND tblIndividus.IndividuChargéDeCompte=tblUser2.index " does not exist.

 

can anyone help me out on there? if you need more info ask... ill put an exemple soon...

[Nerseus]

You'll want to add something like this to the where clause:

AND NOT EXISTS 
(
	SELECT * FROM tblIndividus WHERE IndividuChargéDeCompte = tblUser2.index
)

 

-ner

[Joe Mamma]

MS-SQL, ACCESS, DB2, Sybase, Oracle 10g, this will work. . . and much faster than the not exists.

 

wont work in Oracle 9 or earlier

 

 

SELECT u1.Nom,

u1.Prenom,

Sum(CréditsDeVente) as somme,

u1.Societe,

u2.Nom,

u2.Prenom

FROM

( ( tblIndividus i inner join tblProjet p

on i.NomPrénom = p.ProjetIndividu)

inner join tblUser u1

on u1.index = i.IndividuRessAffairesEst

)

left outer join tblUser u2

on u2.Index = i.IndividuRessAffairesEst

 

where Statut="Terminé - succès" and u2.index is null

 

GROUP BY u1.Nom, u1.Prenom, u1.Societe, u2.Nom, u2.Prenom

ORDER BY Sum(CréditsDeVente) DESC, u1.Societe, u1.Nom, u1.Prenom