SELECT avec 2 niveaux de catégories

**laurentSc** · 12/04/2016, 23h08

Le titre n'est peut-être pas aproprié mais je ne trouve pas mieux. La situation : il y a des machines (ou serveurs) à différentes positions (dans le monde) et de différents types ou dit autrement : à différentes positions dans le monde, il y a des machines de plusieurs types :
exemple :
position A
type 1
machine a
machine b
type 2
machine c
machine d
machine e

position B
type 1
machine e
type 2
machine f
machine g
type 3
machine h
machine i

je voudrais trouver une requête qui affiche pour chaque position et pour chaque type la liste des machines.
Liste des machines à une position donnée et d’un type donné.

Voici la structure actuelle des tables :

table machine
id_machine
name
...

table type_serveur
id_type
type
id_machine

Table position
Id_position
Id_type
positionX
positionY

ça sent la jointure mais je ne sais pas s'il est possible de faire ce que je souhaite en une seule requête.
Merci de m'aider et au besoin de critiquer la structure des tables.
Si ça a une importance, mon SGBD est MySQL.

**Oishiiii** · 12/04/2016, 23h52

Bonsoir,

Petite question concernant la structure des tables, avant de commencer à écrire une requête.

Vous dites qu'il y a différents types de machines.
Dans le sens où, chaque machine a un seul type et pour un type donné il peut y avoir plusieurs machines. C'est juste ?
Si c'est le cas vous ne devriez pas avoir de colonne id_machine dans la table type_serveur.

Idem pour la position.
Si pour une position il peut y avoir plusieurs machines (d'un type donné), vous ne devriez pas avoir id_type dans la table position.

Ces tables existe vraiment actuellement tel que présenté ? Si oui, montrez nous la structure et un exemple au format SQL (CREATE TABLE... et INSERT...), si non il faudra revoir la structure effectivement.
L'exemple ne colle pas avec la structure

**laurentSc** · 13/04/2016, 00h18

Merci pour la réponse.

La table machine existe déja par contre, les tables type_serveur et position, je viens de les créer pour tenter d'atteindre mon objectif. Ayant peu de compétences en SQL, la structure peut ne pas être bonne.
Visiblement, la structure des tables n'est pas bonne. Qu'est-ce qu'il faudrait faire ? Actuellement (j'ai simplifié machine pour ne laisser que les colonnes pertinentes pour le problème) :

Code sql :

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
 
CREATE TABLE IF NOT EXISTS `machines` (
  `name` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `type` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `id_machine` smallint(6) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id_machine`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=427 ;
 
CREATE TABLE IF NOT EXISTS `type_serveur` (
  `id_type` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `typeserveur` varchar(100) COLLATE utf8_bin NOT NULL,
  `id` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`id_type`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ;
 
CREATE TABLE IF NOT EXISTS `position` (
  `id_position` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `id_type` mediumint(8) unsigned NOT NULL,
  `positionX` int(10) unsigned NOT NULL,
  `positionY` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id_position`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=143 ;

**Oishiiii** · 13/04/2016, 09h11

J'ai ré-écris la structure des tables SQL avec le minimum de colonnes également :

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
24
create table serveur_type (
	type_id smallint(6) not null auto_increment
	, type_name varchar(100)
	, CONSTRAINT pk_serveur_type PRIMARY KEY(type_id)
);
 
create table position (
	pos_id mediumint(6) not null auto_increment
	, positionX int(10)
	, positionY int(10)
	, CONSTRAINT pk_position PRIMARY KEY(pos_id)
);
 
create table machine (
	mach_id smallint(6) not null auto_increment
	, mach_name varchar(20)
	, type_id smallint(6)
	, pos_id mediumint(8)
	, CONSTRAINT pk_machine PRIMARY KEY(mach_id)
	, CONSTRAINT fk_machine_type FOREIGN KEY(type_id)
		REFERENCES serveur_type(type_id)
	, CONSTRAINT fk_machine_position FOREIGN KEY(pos_id)
		REFERENCES position(pos_id)
);

Donc deux tables toutes simples; Position et Serveur_Type.
Dans la table machine, il y a 2 colonnes (pos_id et type_id) qui font références aux clé primaires des autres tables. Ce sont des clé étrangères.

Vous pourrez alors écrire par exemple :

Liste des machines à une position donnée et d’un type donné.

Code :

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

1
2
3
4
5
6
7
8
SELECT mach_id, mach_name
FROM machine
	JOIN serveur_type
		ON serveur_type.type_id = machine.type_id
	JOIN position
		ON position.pos_id = machine.pos_id
WHERE positionX = 123 and positionY = 456
  AND type_name = 'ServeurTypeXYZ'

ou directement :

Code :

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

1
2
3
4
SELECT mach_id, mach_name
FROM machine
WHERE pos_id = 859125
  AND type_id = 5514231

C'est l'idée, à tester

**laurentSc** · 13/04/2016, 11h44

Merci de la réponse. En fait, vers 6h du matin, je me suis réveillé avec une idée, donc l'ai notée puis me suis recouché :

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
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
 
Table type 
Id type
Type 
 
Ex
1    type1
2    type2
...
 
Table pos _type 
Idpostype
Idpos
Id_type
 
Ex 
1  1  1
2  1  2
3  1  3
4  2  1
5  2  3
6  2  4
7  3  3
8  3  4
9  4  1
10 4  3
Etc
 
Table pos
Idpos
Position 
 
Table machpostype
Idmachpostyp 
Idpos 
Id_type 
 
Ex 
1    1    1
2    1    2
3    1    3
4    2    2
5    2    4
Etc
Pour
Pos1 type1
Pos1 type2
Pos1 type3 
Pos2 type2
Pos2 type4

Il est vrai que, non habitué aux jointures, je n'ai pas pensé aux clés étrangères. Je vais tester...

**Oishiiii** · 13/04/2016, 14h40

Envoyé par laurentSc

La situation : il y a des machines (ou serveurs) à différentes positions (dans le monde) et de différents types
ou dit autrement : à différentes positions dans le monde, il y a des machines de plusieurs types

Le type de machine est donc une propriété de la machine. Une machine ne peut avoir qu'un seul type.
Idem, la position est comme une propriété de la machine. Une machine ne peut avoir qu'une seule position.

Le type n'a aucun lien avec la position.

Vous n'avez pas besoin de cette table "pos_type".

**laurentSc** · 13/04/2016, 14h44

Petite question avant de tester votre solution : elle modifie la structure de la table machines ; or il se trouve que actuellement, elle compte un paquet de lignes (une centaine ?) ; donc comment en modifier simplement la structure sans avoir à réécrire à la main une centaine d'enregistrements ?

**Oishiiii** · 13/04/2016, 15h05

Pour tester seulement, vous pouvez créer une table à côté, sans toucher votre table actuelle.
Ensuite, en SQL vous pouvez insérer dans une table à partir d'une requête SELECT, pour copier d'une table à l'autre.

Exemple :

Code :

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

1
2
3
INSERT INTO tableDestination(colonne2, colonne3, colonne4)
    SELECT colonneB, colonneC, colonneD
    FROM tableSource

**laurentSc** · 13/04/2016, 17h03

Pour ne pas changer la table machines, je tente de créer une table machinenew, mais ça ne se passe pas bien :

Code sql :

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
CREATE TABLE IF NOT EXISTS `machinenew` (
	mach_id smallint(6) not null auto_increment
	, mach_name varchar(20)
	, type_id smallint(6)
	, pos_id mediumint(8),
  `localization` varchar(30) COLLATE latin1_bin DEFAULT NULL,
  `pays` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT '',
  `entite` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `perimetre` set('Oui','Non') COLLATE latin1_bin DEFAULT NULL,
  `type` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `comment` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `ip` varchar(15) COLLATE latin1_bin DEFAULT NULL,
  `contact_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `contact_phone` varchar(50) COLLATE latin1_bin DEFAULT NULL,
 CONSTRAINT pk_machine PRIMARY KEY(mach_id)
	, CONSTRAINT fk_machine_type FOREIGN KEY(type_id)
		REFERENCES serveur_type(type_id)
	, CONSTRAINT fk_machine_position FOREIGN KEY(pos_id)
		REFERENCES position(pos_id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=427 ;

donne

#1064 - Erreur de syntaxe prÃ¨s de 'position(pos_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUT' Ã la ligne 19

(là, je n'ai pas simplifié ; j'ai mis toutes les colonnes) Quel est le souci ?

**Oishiiii** · 13/04/2016, 17h09

Est-ce que la table position avec la colonne pos_id existe dans la BDD ?

Comme la table machinenew fait référence à la table position, il faut que la deuxième existe avant de créer la première.

**laurentSc** · 13/04/2016, 18h26

J'ai été absorbé par autre chose mais voici ma réponse :

Oui, elle existe :

Code sql :

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

1
2
3
4
5
6
7
 
CREATE TABLE IF NOT EXISTS `position` (
  `pos_id` mediumint(6) NOT NULL AUTO_INCREMENT,
  `positionX` int(10) DEFAULT NULL,
  `positionY` int(10) DEFAULT NULL,
  PRIMARY KEY (`pos_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=6 ;

**Oishiiii** · 13/04/2016, 18h31

Le problème doit venir des types de données qui doivent être strictement identique entre la clé étrangère et la colonne qu'elle référence.

Vous avez
pos_id mediumint(8) dans machinenew
et
`pos_id` mediumint(6) dans position

Ils faudrait que les 2 aient le même type.

Si ce n'est pas ça, je ne vois pas, et il faudrait demander dans le forum MySQL

**laurentSc** · 13/04/2016, 18h40

j'ai mis les 2 à une taille de 8 et le problème reste. Je vais donc poster dans le forum MySQL...

J'attends qu'on me réponde sur le forum MySQL puis je reviendrai ici pour poursuivre la discussion.

**laurentSc** · 14/04/2016, 09h04

Pendant la nuit, on m'a donné la réponse sur le forum MySQL (en gros, INNODB et pas MyISAM et pour les colonnes, ne pas mixer latin1_bin et utf8 entre les 2 tables). Maintenant que j'ai corrigé, la création marche :

Code sql :

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
CREATE TABLE IF NOT EXISTS `machinenew` (
  `mach_id` smallint(6) NOT NULL AUTO_INCREMENT,
  `mach_name` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `type_id` smallint(6) DEFAULT NULL,
  `pos_id` smallint(6) DEFAULT NULL,
  `localization` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `pays` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `entite` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `perimetre` set('Oui','Non') COLLATE latin1_general_ci DEFAULT NULL,
  `type` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `comment` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `ip` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `contact_name` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `contact_phone` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`mach_id`),
  KEY `fk_machine_position` (`pos_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED AUTO_INCREMENT=1 ;

avec

Code sql :

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

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `position` (
  `pos_id` smallint(6) NOT NULL AUTO_INCREMENT,
  `positionX` int(11) DEFAULT NULL,
  `positionY` int(11) DEFAULT NULL,
  PRIMARY KEY (`pos_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED AUTO_INCREMENT=6 ;

mais l'étape suivante échoue :

Code sql :

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
24
25
INSERT INTO machinenew(	mach_id
	, mach_name 
	, type_id 
	, pos_id ,
  `localization` ,
  `pays` ,
  `entite` ,
  `perimetre`,
  `type`,
  `comment`,
  `ip`,
  `contact_name`,
  `contact_phone` )
    SELECT    `name` ,
  `localization` ,
  `pays`,
  `entite`,
  `perimetre` ,
  `type`,
  `id`,
  `comment`,
  `ip`,
  `contact_name` ,
  `contact_phone` 
    FROM machines

donne

#1136 - Column count doesn't match value count at row 1

Comment faire ?

**escartefigue** · 14/04/2016, 09h11

Bonjour,

Le message d'erreur est clair : le nombre de colonnes que vous voulez insérer ne correspond pas au nombre de valeurs que vous fournissez
Vous avez (si j'ai bien compté) 13 colonnes déclarées dans votre insert, et seulement 11 valeurs communiquées au travers du select

**laurentSc** · 14/04/2016, 10h21

Oui, je me disais bien que le problème était là, mais en gros, je voudrais passer de machine à machinenew en rajoutant 2 colonnes sans être obligé de corriger chaque enregistrement (au nombre d'une centaine) ; est-ce possible ?

**escartefigue** · 14/04/2016, 10h37

Plusieurs solutions

Soit vous ne mentionnez pas les colonnes dont vous ignorez la valeur, et il s'agit de colonnes "not null with default", auquel cas la valeur par défaut sera appliquée par le SGBD

Soit vous faites une jointure avec d'autre(s) table(s) pour récupérer les valeurs à insérer

Soit vous attribuez des valeurs "en dur"

...

**laurentSc** · 14/04/2016, 13h44

Je n'ai pas eu le temps de répondre plus tôt...

J'ai adopté une solution non évoquée (mais plus simple à réaliser). J'ai ajouté à la table machine les 2 colonnes supplémentaires :

Code sql :

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

alter table machine type_id smallint(6) after name

et

Code sql :

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

alter table machine pos_id smallint(6) after type_id

mais après ma requête d'insertion (celle du post #14),

#1452 - Cannot add or update a child row: a foreign key constraint fails (`su_dev_eis`.`machinenew`, CONSTRAINT `fk_machine_position` FOREIGN KEY (`pos_id`) REFERENCES `position` (`pos_id`) ON UPDATE CASCADE)

et pour rappel :

Code sql :

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
CREATE TABLE IF NOT EXISTS `machinenew` (
  `mach_id` smallint(6) NOT NULL AUTO_INCREMENT,
  `mach_name` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `type_id` smallint(6) DEFAULT NULL,
  `pos_id` smallint(6) DEFAULT NULL,
  `localization` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `pays` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `entite` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `perimetre` set('Oui','Non') COLLATE latin1_general_ci DEFAULT NULL,
  `type` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `comment` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `ip` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `contact_name` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `contact_phone` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`mach_id`),
  KEY `fk_machine_position` (`pos_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPRESSED AUTO_INCREMENT=4 ;

**Oishiiii** · 14/04/2016, 13h54

Vous avez une contrainte de clé étrangère :

Code :

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

CONSTRAINT `fk_machine_position` FOREIGN KEY (`pos_id`) REFERENCES `position` (`pos_id`)

Cela veut dire que toutes les valeurs de la colonne pos_id dans la machine doivent exister d'abord dans la colonne pos_id de la table position.

Donc remplissez d'abord la table position, et ensuite vous pourrez mettre la valeur de pos_id qui va bien dans la table machine.

**laurentSc** · 14/04/2016, 14h00

Mais la colonne pos_id de la table position existe déjà...Bon seulement 5 enregistrements ???

SELECT avec 2 niveaux de catégories

Langage SQL

Discussions similaires

Partager

Partager