[MySQL] Requête sur table de jointure avec not in ou not exists

**GueloSuperStar** · 07/03/2013, 11h36

Bonjour,
je voudrais faire des requêtes pour récupérer tous les id_a qui ont pour id_b la valeur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
- NOT IN (1) OR NOT IN (2) AND IN (4) => id_a : 5
- NOT IN (1) AND NOT IN (2) AND IN (4) => id_a : 4,5

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
+------+------+
| id_a | id_b |
+------+------+
|    1 |   1 |
|    1 |   2 |
|    1 |   3 |
|    1 |   4 |
|    1 |   5 |
|    2 |   1 |
|    2 |   2 |
|    2 |   3 |
|    2 |   4 |
|    3 |   1 |
|    3 |   2 |
|    3 |   3 |
|    3 |   6 |
|    3 |   7 |
|    4 |   1 |
|    4 |   4 |
|    4 |   9 |
|    5 |   4 |
|    5 |   9 |
+------+------+

Merci

**al1_24** · 07/03/2013, 11h53

Dans la première expression, qui combine des OR et des AND, il faudrait ajouter des parenthèses pour savoir comment associer les conditions.

**GueloSuperStar** · 07/03/2013, 12h08

Oui c'est vrai ,

- ( NOT IN (1) OR NOT IN (2) ) AND (IN (4)) => id_a : 5
- ( NOT IN (1) OR NOT IN (2) ) AND ( IN (4) OR IN(9) )=> id_a : 5
- NOT IN (1) AND NOT IN (2) AND IN (4) => id_a : 4,5

**CinePhil** · 07/03/2013, 12h29

?

**GueloSuperStar** · 07/03/2013, 13h35

Nan pas résolu

,

Je cherche la requête qui permettrait de récupérer :
- Dans le premier cas les id_a qui n'ont pas id_b = 1 ou ib_b = 2 mais id_b = 4
- Dans le 2eme cas les id_a qui n'ont pas id_b = 1 ou ib_b = 2 ou id_b = 4
ou id_b = 9
- Dans le 3eme cas les id_a qui n'ont pas id_b = 1 ET ib_b = 2 mais id_b = 4

Est-ce assez clair ?

Merci

**al1_24** · 07/03/2013, 13h59

Qu'est-ce que tu as déjà fait ?
Où rencontres-tu des erreurs ? Lesquelles ?

**GueloSuperStar** · 07/03/2013, 14h14

J'ai essayé ce type de requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT id_a
    -> FROM `1_test`
    -> WHERE (id_b NOT IN(1) OR id_b NOT IN(2)) AND id_b IN(4)
    -> GROUP BY id_a;
+------+
| id_a |
+------+
|    1 | -> erreur car id_b = 1 ou  id_b = 2
|    2 | -> erreur car id_b = 1 ou  id_b = 2
|    4 |-> erreur car id_b = 1
|    5 | -> bon car ->  id_b != 1 ou id_b != 2 and id_b = 4
+------+

**CinePhil** · 07/03/2013, 14h21

Je cherche la requête qui permettrait de récupérer :
- Dans le premier cas les id_a qui n'ont pas id_b = 1 ou ib_b = 2 mais id_b = 4

Exprimons le autrement :
Sélectionner les id_a pour lesquels id_b = 4 et il n'existe pas de ligne du même id_a dont l'id_b est dans l'ensemble {1, 2}.

Traduction en SQL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT t1.id_a
FROM 1_test t1
WHERE t1.id_b = 4
	AND NOT EXISTS 
	(
		SELECT *
		FROM 1_test t2
		WHERE t2.id_a = t1.id_a
			AND t2.id_b IN (1, 2)
	)

Les autres sont sur le même principe.

**GueloSuperStar** · 07/03/2013, 14h22

Ou encore ce genre de requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT id_a FROM `1_test`
GROUP BY id_a
HAVING	COUNT(
	DISTINCT(
		CASE
			when id_b not in (1) then 1
			when id_b not in (2) then 2
			when id_b in (4) then 3
		END
	)
) = 3;
Empty set (0,00 sec)

**GueloSuperStar** · 07/03/2013, 15h03

Oui avec NOT IN ou NOT EXISTS mais j'ai pas retenu cette solution car voici les temps pour ces requêtes :

Requête NOT IN :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT rr.rs_id FROM table1 AS `rr`
INNER JOIN table2 as sc on rr.rm_r_id=sc.r_id
WHERE sc.rm_id = 14
AND rr.rs_id NOT IN(
	SELECT rr2.rs_id FROM table1 as rr2 
	INNER JOIN table2 as sc2 on rr2.rm_r_id=sc2.r_id
	WHERE sc2.rm_id IN (64001,64002)
)
 
81549 rows in set (14,93 sec)

Explain :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
+----+--------------------+-------+--------+---------------+---------+---------+----------------------------+------+-------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                        | rows | Extra       |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------------+------+-------------+
|  1 | PRIMARY            | sc    | ref    | PRIMARY,rm_id | rm_id   | 3       | const                      |    1 |             |
|  1 | PRIMARY            | rr    | ref    | rm_r_id       | rm_r_id | 3       | sc.r_id     | 1152 | Using where |
|  2 | DEPENDENT SUBQUERY | rr2   | ref    | rs_id,rm_r_id | rs_id   | 3       | func                       |   54 |             |
|  2 | DEPENDENT SUBQUERY | sc2   | eq_ref | PRIMARY,rm_id | PRIMARY | 3       | rr2.rm_r_id |    1 | Using where |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------------+------+-------------+

Requête NOT EXISTS :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT rr.rs_id FROM `table1` AS `rr`
INNER JOIN table2 as sc on rr.rm_r_id=sc.r_id
WHERE sc.rm_id = 14
AND NOT EXISTS(
	SELECT NULL FROM table1 as rr2 
	INNER JOIN table2 as sc2 on rr2.rm_r_id=sc2.r_id
	WHERE rr2.rs_id = rr.rs_id AND sc2.rm_id IN (64001,64002)
)
 
81549 rows in set (15,08 sec)

Explain :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
+----+--------------------+-------+--------+---------------+---------+---------+----------------------------+------+-------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                        | rows | Extra       |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------------+------+-------------+
|  1 | PRIMARY            | sc    | ref    | PRIMARY,rm_id | rm_id   | 3       | const                      |    1 |             |
|  1 | PRIMARY            | rr    | ref    | rm_r_id       | rm_r_id | 3       | sc.r_id     | 1152 | Using where |
|  2 | DEPENDENT SUBQUERY | rr2   | ref    | rs_id,rm_r_id | rs_id   | 3       | rr.rs_id    |   54 |             |
|  2 | DEPENDENT SUBQUERY | sc2   | eq_ref | PRIMARY,rm_id | PRIMARY | 3       | rr2.rm_r_id |    1 | Using where |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------------+------+-------------+

show index from table1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table1 |          0 | PRIMARY  |            1 | rr_id       | A         |    21913471 |     NULL | NULL   |      | BTREE      |         |               |
| table1 |          1 | rs_id    |            1 | rs_id       | A         |      405805 |     NULL | NULL   |      | BTREE      |         |               |
| table1 |          1 | rm_r_id  |            1 | rm_r_id     | A         |       19022 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

show index from table2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table2 |          0 | PRIMARY   |            1 | r_id        | A         |       21530 |     NULL | NULL   |      | BTREE      |         |               |
| table2 |          1 | rm_id     |            1 | rm_id       | A         |       21530 |     NULL | NULL   |      | BTREE      |         |               |
| table2 |          1 | r_group   |            1 | r_group     | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| table2 |          1 | qm_id     |            1 | qm_id       | A         |        1266 |     NULL | NULL   |      | BTREE      |         |               |
| table2 |          1 | q_id      |            1 | q_id        | A         |         717 |     NULL | NULL   |      | BTREE      |         |               |
| table2 |          1 | q_id_next |            1 | q_id_next   | A         |         717 |     NULL | NULL   |      | BTREE      |         |               |
| table2 |          1 | q_first   |            1 | q_first     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Merci.

**GueloSuperStar** · 07/03/2013, 15h22

J'ai peut être cette requête si je fais pas n'importe quoi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT id_a FROM `1_test`
WHERE id_b in (4,1,2)
GROUP BY id_a
HAVING	COUNT(
	DISTINCT(
		CASE
			when id_b in (4) then 1
			when id_b in (1,2) then 2
		END
		)) =1;
+------+
| id_a |
+------+
|    3 |
|    5 |
+------+