1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177
|
$NutriPatho="SELECT obli.numDossier, obli.localisationPrim,
obli.nomP, obli.prenomP, obli.dateN, obli.sexe, obli.rue, obli.codeP,
obli.ville, obli.numDossierInstitution, obli.institution, obli.service,
NutriPatho.poids, NutriPatho.taille, NutriPatho.BMI, NutriPatho.MNA,
NutriPatho.PerteKg, NutriPatho.cardiaque, NutriPatho.coronaire,
NutriPatho.rythme, NutriPatho.respiratoire, NutriPatho.diabete,
NutriPatho.osteo, NutriPatho.dysthyroidie, NutriPatho.neurologie,
NutriPatho.thrombo, NutriPatho.hta
FROM
(SELECT q.numDossier, q.localisationPrim, p.nomP,
p.prenomP, p.dateN, p.sexe, p.rue, p.codeP, p.ville,
e.numDossierInstitution,eq.institution, eq.service
FROM questionnaire q, patient p, cancer c, envoi e, equipe eq
WHERE p.IdPatient=q.IdPatient and q.localisationPrim=c.localisationPrim and p.IdPatient=e.IdPatient
and e.IdEq=eq.IdEq) obli
INNER JOIN
(SELECT Nu.numDossier, Nu.poids, Nu.taille, Nu.BMI, Nu.MNA,
Nu.PerteKg, patho.cardiaque, patho.coronaire,
patho.rythme, patho.respiratoire, patho.diabete, patho.osteo,
patho.dysthyroidie, patho.neurologie, patho.thrombo, patho.hta
FROM
(SELECT n2.numDossier, n1.poids, n1.taille, n1.BMI, n1.MNA,
n2.PerteKg
FROM
(SELECT nutri1.numDossier, nutri1.poids, nutri1.taille,
nutri2.BMI, nutri2.MNA
FROM
(SELECT P.numDossier, P.poids, T.taille
FROM
(SELECT pa.numDossier, qu.valeur as poids
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='nutrition' AND pa.sousPartie is NULL
AND qu.libelle='poids' order by pa.numDossier) P
INNER JOIN
(SELECT pa.numDossier, qu.valeur as taille
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='nutrition' AND pa.sousPartie is NULL
AND qu.libelle='taille' order by pa.numDossier) T
ON P.numDossier=T.numDossier
ORDER BY P.numDossier) nutri1
INNER JOIN
(SELECT B.numDossier, B.BMI, M.MNA
FROM
(SELECT pa.numDossier, qu.valeur as BMI
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='nutrition' AND pa.sousPartie is NULL
AND qu.libelle='BMI' order by pa.numDossier) B
INNER JOIN
(SELECT pa.numDossier, qu.valeur as MNA
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='nutrition' AND pa.sousPartie is NULL
AND qu.libelle='MNA' order by pa.numDossier) M
ON B.numDossier=M.numDossier
ORDER BY B.numDossier) nutri2
ON nutri1.numDossier=nutri2.numDossier
ORDER BY nutri1.numDossier) n1
INNER JOIN
(SELECT pa.numDossier, qu.libelle as PerteKg
FROM question qu
INNER JOIN partie pa
ON qu.IdPartie=pa.IdPartie
WHERE pa.nomPartie='nutrition'
and qu.libelle in ('oui', 'non', 'ne sait pas')
and qu.valeur='1' order by pa.numDossier) n2
ON n1.numDossier=n2.numDossier
ORDER BY n1.numDossier) Nu
INNER JOIN
(SELECT patho1.numDossier, patho1.cardiaque, patho1.coronaire,
patho1.rythme, patho1.respiratoire, patho1.diabete, patho2.osteo,
patho2.dysthyroidie, patho2.neurologie, patho2.thrombo, patho2.hta
FROM
(SELECT P1.numDossier, P1.cardiaque, P1.coronaire, P2.rythme,
P2.respiratoire, P2.diabete
FROM
(SELECT card.numDossier, card.cardiaque, coro.coronaire
FROM
(SELECT pa.numDossier, qu.libelle as cardiaque
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='pathologie'
AND pa.sousPartie='cardiaque'
AND qu.valeur='1' order by pa.numDossier) card
INNER JOIN
(SELECT pa.numDossier, qu.libelle as coronaire
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='pathologie'
AND pa.sousPartie='coronaire'
AND qu.valeur='1' order by pa.numDossier) coro
ON card.numDossier=coro.numDossier) P1
INNER JOIN
(SELECT rythme.numDossier, rythme.rythme, P.respiratoire,
P.diabete
FROM
(SELECT pa.numDossier, qu.libelle as rythme
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='pathologie' AND pa.sousPartie='rythme'
AND qu.valeur='1' order by pa.numDossier) rythme
INNER JOIN
(SELECT respi.numDossier, respi.respiratoire,
diabete.diabete
FROM
(SELECT pa.numDossier, qu.libelle as respiratoire
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='pathologie'
AND pa.sousPartie='respiratoire'
AND qu.valeur='1' order by pa.numDossier) respi
INNER JOIN
(SELECT pa.numDossier, qu.libelle as diabete
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='pathologie'
AND pa.sousPartie='diabete'
AND qu.valeur='1' order by pa.numDossier) diabete
ON respi.numDossier=diabete.numDossier) P
ON rythme.numDossier=P.numDossier) P2
ON P1.numDossier=P2.numDossier) patho1
INNER JOIN
(SELECT P3.numDossier, P3.osteo, P3.dysthyroidie,
P4.neurologie,
P4.thrombo, P4.hta
FROM
(SELECT osteo.numDossier, osteo.osteo, dysthy.dysthyroidie
FROM
(SELECT pa.numDossier, qu.libelle as osteo
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='pathologie'
AND pa.sousPartie='osteo-articulaire'
AND qu.valeur='1' order by pa.numDossier) osteo
INNER JOIN
(SELECT pa.numDossier, qu.libelle as dysthyroidie
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='pathologie'
AND pa.sousPartie='dysthyroidie'
AND qu.valeur='1' order by pa.numDossier) dysthy
ON osteo.numDossier=dysthy.numDossier) P3
INNER JOIN
(SELECT neuro.numDossier, neuro.neurologie, Px.thrombo,
Px.hta
FROM
(SELECT pa.numDossier, qu.libelle as neurologie
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='pathologie'
AND pa.sousPartie='neurologie'
AND qu.valeur='1' order by pa.numDossier) neuro
INNER JOIN
(SELECT thrombo.numDossier, thrombo.thrombo, hta.hta
FROM
(SELECT pa.numDossier, qu.libelle as thrombo
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='pathologie'
AND pa.sousPartie='thrombo-embolique'
AND qu.valeur='1' order by pa.numDossier) thrombo
INNER JOIN
(SELECT pa.numDossier, qu.libelle as hta
FROM Partie pa INNER JOIN Question qu
ON pa.IdPartie=qu.IdPartie
WHERE pa.nomPartie='pathologie' AND pa.sousPartie='hta'
AND qu.valeur='1' order by pa.numDossier) hta
ON thrombo.numDossier=hta.numDossier) Px
ON neuro.numDossier=Px.numDossier) P4
ON P3.numDossier=P4.numDossier) patho2
ON patho1.numDossier=patho2.numDossier) patho
ON Nu.numDossier=patho.numDossier) NutriPatho
ON obli.numDossier=NutriPatho.numDossier
ORDER BY obli.numDossier"; |