Sebastien | Mon idée de solution : Code :
- -- Création Cas (2,4) minus (3,4) => cas 2
- SELECT j.usert --Cas 2, 4
- FROM ttsebtest_j j
- WHERE NOT EXISTS (SELECT usert, ROLE
- FROM ttsebtest_jm1 jm1
- WHERE j.usert = jm1.usert AND j.ROLE = jm1.ROLE)
- MINUS
- SELECT jm1.usert --Cas 3, 4
- FROM ttsebtest_jm1 jm1
- WHERE NOT EXISTS (SELECT usert, ROLE
- FROM ttsebtest_j j
- WHERE j.usert = jm1.usert AND j.ROLE = jm1.ROLE)
- -- Suppression Cas (3,4) minus (2,4) => cas 3
- /* Formatted on 2009/02/24 12:19 (Formatter Plus v4.8.5) */
- SELECT jm1.usert --Cas 3, 4
- FROM ttsebtest_jm1 jm1
- WHERE NOT EXISTS (SELECT usert, ROLE
- FROM ttsebtest_j j
- WHERE j.usert = jm1.usert AND j.ROLE = jm1.ROLE)
- MINUS
- SELECT j.usert --Cas 2, 4
- FROM ttsebtest_j j
- WHERE NOT EXISTS (SELECT usert, ROLE
- FROM ttsebtest_jm1 jm1
- WHERE j.usert = jm1.usert AND j.ROLE = jm1.ROLE)
- --Création et Suppression
- -- cas (2,4) union (3,4) minus (cas 2) minus (cas 3) => cas 4
- SELECT j.usert --Cas (2,4)
- FROM ttsebtest_j j
- WHERE NOT EXISTS (SELECT usert, ROLE
- FROM ttsebtest_jm1 jm1
- WHERE j.usert = jm1.usert AND j.ROLE = jm1.ROLE)
- UNION
- SELECT jm1.usert --Cas (3,4)
- FROM ttsebtest_jm1 jm1
- WHERE NOT EXISTS (SELECT usert, ROLE
- FROM ttsebtest_j j
- WHERE j.usert = jm1.usert AND j.ROLE = jm1.ROLE)
- MINUS
- (SELECT j.usert
- FROM ttsebtest_j j
- WHERE NOT EXISTS (SELECT usert, ROLE
- FROM ttsebtest_jm1 jm1
- WHERE j.usert = jm1.usert AND j.ROLE = jm1.ROLE)
- MINUS
- SELECT jm1.usert
- FROM ttsebtest_jm1 jm1
- WHERE NOT EXISTS (SELECT usert, ROLE
- FROM ttsebtest_j j
- WHERE j.usert = jm1.usert AND j.ROLE = jm1.ROLE))
- MINUS
- (SELECT jm1.usert
- FROM ttsebtest_jm1 jm1
- WHERE NOT EXISTS (SELECT usert, ROLE
- FROM ttsebtest_j j
- WHERE j.usert = jm1.usert AND j.ROLE = jm1.ROLE)
- MINUS
- SELECT j.usert
- FROM ttsebtest_j j
- WHERE NOT EXISTS (SELECT usert, ROLE
- FROM ttsebtest_jm1 jm1
- WHERE j.usert = jm1.usert AND j.ROLE = jm1.ROLE))
| Ensuite au vu des résultats je refais des jointures la ou il faut Message édité par Sebastien le 24-02-2009 à 12:21:24
|