Le sql, c'est vachement simple

EsKel -  
 soos -
On ne peut plus simple, est-ce qu'il est possible de simplifier ca ??

SELECT tblUser.lngUserID, tblUser.txtUserNickName, tblUser.txtUserEmail, tblOccupation.txtOccupation, tblState.txtSate, tblRegion.txtRegion, DATEDIFF(year,
tblUser.dteUserBirthday, GETDATE()) AS intAge, ISNULL(tblTotInterest.intTotInterest, 0) AS intSumInterest,
ISNULL(tblTotPersonnality.intTotPersonnality, 0) AS intSumPersonnality, ISNULL(tblTotPersonnality.intTotPersonnality, 0)
+ ISNULL(tblTotInterest.intTotInterest, 0) AS intTotOption
FROM (SELECT tblProfile.lngProfileID, COUNT(*) AS intTotInterest
FROM tblProfile INNER JOIN
tblProfileInterest ON tblProfile.lngProfileID = tblProfileInterest.lngProfileID
GROUP BY tblProfile.lngProfileID) tblTotInterest FULL OUTER JOIN
(SELECT tblProfile.lngProfileID, COUNT(*) AS intTotPersonnality
FROM tblProfile INNER JOIN
tblProfilePersonnality ON tblProfile.lngProfileID = tblProfilePersonnality.lngProfileID INNER JOIN
tblPersonnality ON tblProfilePersonnality.lngProfileID = tblPersonnality.lngPersonnalityID
GROUP BY tblProfile.lngProfileID, tblProfile.lngUserID) tblTotPersonnality RIGHT OUTER JOIN
tblUser INNER JOIN
tblProfile ON tblUser.lngUserID = tblProfile.lngUserID INNER JOIN
tblSearchProfile ON tblUser.lngUserID = tblSearchProfile.lngUserID INNER JOIN
tblSearchProfileType ON tblSearchProfile.lngSearchProfileID = tblSearchProfileType.lngSearchProfileID INNER JOIN
tblRegion ON tblUser.lngRegionID = tblRegion.lngRegionID INNER JOIN
tblOccupation ON tblUser.lngOccupationID = tblOccupation.lngOccupationID INNER JOIN
tblState ON tblRegion.lngStateID = tblState.lngStateID ON tblTotPersonnality.lngProfileID = tblProfile.lngProfileID ON
tblTotInterest.lngProfileID = tblTotPersonnality.lngProfileID
GROUP BY ISNULL(tblTotInterest.intTotInterest, 0), ISNULL(tblTotPersonnality.intTotPersonnality, 0), tblOccupation.txtOccupation, tblOccupation.lngOccupationID,
tblRegion.txtRegion, DATEDIFF(year, tblUser.dteUserBirthday, GETDATE()), tblUser.txtUserEmail, tblUser.lngUserID,
ISNULL(tblTotPersonnality.intTotPersonnality, 0) + ISNULL(tblTotInterest.intTotInterest, 0), tblUser.txtUserNickName, tblState.txtSate,
tblRegion.lngRegionID, tblRegion.lngStateID
ORDER BY ISNULL(tblTotInterest.intTotInterest, 0) DESC, ISNULL(tblTotPersonnality.intTotPersonnality, 0) DESC
A voir également:

4 réponses

EsKel
 
J'ai oublie que je vais devoir integre des clauses where pour l'occupation , les interets (tblProfilInterest) , les traits de personnalite (tblProfilPersonnality) , l'age (tblUser), et le type de profil recherche(tblSearchProfileType)
0
choubaka Messages postés 39986 Date d'inscription   Statut Modérateur Dernière intervention   2 105
 
j'ai mal au neurone pour toi

Chouba
"Obsédé des travaux manuels non pratiquant"
0
Omen
 
Moi aussi mais c'est un bon exemple de complexité !!! Ca casse tout comme diraient certains ...
0
soos
 
je te conseille d'aller pas à pas

ce n'est pas pour te décourager ms je ne sais pas comment tu vas faire.:))).

soos.
0