écrire une requête

[zhebulonn]

Bonjour,

Je ne fais pas beaucoup de sql et je dois écrire une requête un peu complexe (pour moi en tout cas )

Je vais prendre un exemple :

Une table "personne" avec un champ id et un champ nom.

Une table "hotel" avec un champ id, un champ nom.

Une table "personne_a_dormi" avec un id, une clef étrangère idPersonne et une clef étrangère idHotel et un champ a_dormi.

Ce que je voudrais, comme résultat de requête, c'est quelque chose comme ça :
| personne | |hotel1| |hotel2| |hotel3| |hotel4| .....
personne1 oui non oui .....
personne2 non ....
...

Je ne sais pas si je suis clair ?

En gros, je voudrais, pour toutes les personnes, voir tous les hotels.

Zhebulonn

[Waldar]

Si possible, faites la mise en page dans votre application cliente, car le SQL est un langage déclaratif où le nombre de colonnes doit être connu avant d'écrire la requête.

Sinon il s'agit simplement de deux jointures :

Code :

1
2
3
4
5
6
SELECT per.nom, hot.nom
  FROM personne per
       INNER JOIN personne_a_dormi pad
         ON pad.idPersonne = per.id
       INNER JOIN hotel hot
         ON hot.id = pad.idHotel

[ericd69]

Il a raison, on ne peut faire un select sur un nombre indéterminé de colonnes, la seule solution est une procédure stockée. En mysql ça donnerait ça:

Code sql :

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
-- uniquement pour le test
DROP DATABASE IF EXISTS hotels;
CREATE DATABASE hotels DEFAULT character SET utf8 collate utf8_general_ci;
-- on sélectionne la bd quoi qu'il arrive pour bien travailler avec elle par défaut
USE hotels; -- à toi d'adapter
 
-- on définit les tables et on les remplit uniquement pour le test
CREATE TABLE hotel(
	id		int(4) NOT NULL AUTO_INCREMENT,
	nom	varchar(255) NOT NULL,
	constraint pk_hotel PRIMARY KEY(id)
)engine=myisam AUTO_INCREMENT=1;
CREATE TABLE personne(
	id		int(4) NOT NULL AUTO_INCREMENT,
	nom	varchar(255) NOT NULL,
	constraint pk_personne PRIMARY KEY(id)
)engine=myisam AUTO_INCREMENT=1;
-- la table de liaison, je ne mets pas de contrainte dessus car je la crée avec le moteur myisam qui ne s'en occupe pas
CREATE TABLE personne_a_dormi(
	id				int(4) NOT NULL AUTO_INCREMENT,
	idPersonne 	int(4) NOT NULL,
	idHotel		int(4) NOT NULL,
	constraint pk_personne_a_dormi PRIMARY KEY(id)
)engine=myisam AUTO_INCREMENT=1;
 
INSERT INTO hotel(nom)VALUES("hotel1"),("hotel2"),("hotel3");
INSERT INTO personne(nom)VALUES("personne1"),("personne2"),("personne3"),("personne4");
INSERT INTO personne_a_dormi(idPersonne,idHotel)VALUES(1,1),(1,2),(2,2),(3,1),(3,3),(4,1),(4,2),(4,3);
 
-- on définit la procédure stockée
DROP procedure IF EXISTS recapitulatif;
delimiter |
CREATE procedure recapitulatif()
begin
	declare ligne text DEFAULT "personne";
	declare tmp varchar(255);
	declare done,test bool;
	declare cpers,chotel int(4);
	declare colonnes cursor FOR SELECT nom FROM hotel;
	declare lignes cursor FOR SELECT id,nom FROM personne;
	declare lithotel cursor FOR SELECT id FROM hotel;
	declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	-- on crée l'entête
	open colonnes;
	SET done=false;
	repeat
		fetch colonnes INTO tmp;
		IF NOT done then
			SET ligne=concat(ligne,",",tmp);
		end IF;
	until done end repeat;
	close colonnes;
	SELECT ligne AS entete;
	SET done=false;
	open lignes;
	repeat
		fetch lignes INTO cpers,tmp;
		IF NOT done then
			SET test=done;
			SET ligne=tmp;
			open lithotel;
			repeat
				fetch lithotel INTO chotel;
				IF NOT done then
					SELECT IF(count(id)>0,"Oui","Non") INTO tmp FROM personne_a_dormi WHERE idPersonne=cpers AND idHotel=chotel;
					SET ligne=concat(ligne,",",tmp);
				end IF;
			until done end repeat;
			close lithotel;
			SELECT ligne;
			SET done=test;
		end IF;
	until done end repeat;
	close lignes;
end|
delimiter ;
-- fin de la procédure stockée
 
-- on teste en appelant la procédure
call recapitulatif();

Le résultat est ce que tu voulais, du moins au mieux de ce que l'on peut pondre avec les contraintes de sql
Un résultat d'une ligne qui est l'entête, suivie d'un résultat d'une ligne par personne, les valeurs sont séparées par une virgule.

Tu dois donc pouvoir manipuler une requête à résultats multiples (mysqli ou pdo pour mysql principalement avec un petit réglage)...

A toi d'adapter ceci à ton sgbd si c'est pas mysql car chacun fait sa vie vis à vis de l'écriture sql et tu trouveras un autre pour t'aider à le faire

Si tu veux que l'intégrité référentielle soit respectée sur la table de liaison il faut utiliser le moteur innodb à la place de myisam sur cette table.

voilà

[dehorter olivier]

bonjour

PIVOT fait ce genre de choses (sous MS SQL).

Penses a jeter un coup d'oeuil sur le forum avec ce mot

a+
olivier

[zhebulonn]

Merci beaucoup pour vos réponses.

Je n'ai pas encore eu le temps d'essayer.

Je vous tiens au courant.

[ericd69]

à savoir que avec peu de modifications tu peux les différents résultats en un seul au format xml en rajoutant quelques concat, après tout dépend de tes besoins

[iberserk]

Si possible, faites la mise en page dans votre application cliente, car le SQL est un langage déclaratif où le nombre de colonnes doit être connu avant d'écrire la requête.

J'abonde...
Cela vous évitera le code de ericd69 qui est tout sauf une solution...

avec les contraintes de sql

Permettez moi de rigoler... sql n'a pour le coup que les contraintes qu'on veut bien lui donner, en l’occurrence souvent une modélisation perfectible...

La 'solution' côté SQL serait un pivot mais je crois que (lui aussi) n'est pas implémenté dans mySQL.

[punkoff]

Envoyé par iberserk

La 'solution' côté SQL serait un pivot mais je crois que (lui aussi) n'est pas implémenté dans mySQL.

Bonjour,

c'est implémenté :
http://dev.mysql.com/doc/refman/5.0/...n_group-concat

[ericd69]

la réduction des échanges sgbd/application peut être aussi un critère...

y a jamais une seule solution pour faire les chose et une seule optimisation... ça dépend de tes besoins

toutes les solutions proposées ici se valent mais certaines peuvent être plus adaptées selon tes contraintes (principalement les caractéristiques de fonctionnement de ton mysql)...

ps: j'adore l'intégrisme de certains!

[iberserk]

ps: j'adore l'intégrisme de certains!

Pas de problème il est assumé à 100%, etpour cause:

Je passe mon temps à reprendre ce genre de code une fois les applications tombées en prod...

Et je peut vous dire que je suis 'doux' certains machent moins leurs mots...

[ericd69]

Envoyé par iberserk

Pas de problème il est assumé à 100%

y a pas de soucis moi aussi ça m'arrive mais il faut se dire qu'il n'y a pas qu'un chemin pour aller au but

et parfois, quand tu vois comment sont codés des trucs genre phpbb, dotclear ou autres... ça fait peur

après faut pas être trop formaliste non plus... je l'ai pas pris mal...

Perso, utiliser une subtilité de programmation pour limiter l'usage de bp et donner un résultat qui est récupéré avec un simple split pour etre transformer en tableau en php je trouve ça plus pas si mal... le langage est un outil pas forcément une fin en soit et la répartition sgbd/appli pour le traitement peut toujours se discuter... Le traitement que tu proposes est tout aussi valable.

[SQLpro]

Envoyé par iberserk

Pas de problème il est assumé à 100%, et pour cause:

Je passe mon temps à reprendre ce genre de code une fois les applications tombées en prod...

Et je peut vous dire que je suis 'doux' certains mâchent moins leurs mots...

Tu m'as appelé ??? ;-)

A +

[iberserk]

Tu m'as appelé ??? ;-)

A +

Moi? pas du tout