select avec des variables fixes

Version imprimable

Bonjour,
voici la structure de ma table:
Code:

1 2 3 4 5 6 7 8 9 CREATE TABLE `question` ( `NIPATIENT` int(11) NOT NULL default '0', `NIQ` char(3) NOT NULL default '', `DATE ENTREE` date NOT NULL default '2008-02-02', `LIBABR` varchar(100) NOT NULL default '', `REPONSE` text, `TYPE` int(1) NOT NULL default '0', FULLTEXT KEY `NIQ` (`NIQ`) ) TYPE=MyISAM;
Comment peut on faire des requêtes du genre
Select * from `question`
union `3`,`19`,`2008-03-15`,`test`,NULL,`0`

pour avoir en plus des données de la table, la ligne de la requête,
quelque chose du genre:
Code:

1 2 3 4 5 1,11,2008-02-02, IDENTITE, NULL, 0 1, 12, 2008-02-02,Numéro de dossier appareil,EFR1, 1 1,13,2008-02-02,Date de l'examen, 21/03/2004, 1 1,15,2008-02-02,Heure de l'examen,12:30, 1 3,19,2008-03-15,test,NULL,0
je n'ai pas le droit d'ajouter des données dans la base!

Je vous remercie d'avance pour votre aide.

31/03/2008, 00h33
Alain Defrance

Je n'ai pas vraiment compris ce que tu veux faire.
Par contre UNION se trouve entre deux SELECT tu n'as pas le droit (du moins que je sache) de mettre un UNION au milieu d'un SELECT.

Merci pour votre aide,
en fait je souhaite faire une requête du genre:

Code:

1
2
SELECT * FROM `question`
union select `NIPATIENT`,"18",`DATE ENTREE`,"test",NULL,"0" from `question`

par exemple

et si ma table contient les données:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
NIPATIENT  NIQ  DATE ENTREE  LIBABR  REPONSE  TYPE 
"1","11","2008-02-02","IDENTITE",NULL,"0"
"1","12","2008-02-02","Numéro de dossier appareil","EFR1","1"
"1","13","2008-02-02","Date de l'examen","21/03/2004","1"
"1","15","2008-02-02","Heure de l'examen","12:30","1"
"1","16","2008-02-02","Date de naissance","21/07/1977","1"
"1","17","2008-02-02","TABAC",NULL,"0"
"1","19","2008-02-02","fumeur","non","1"
"1","20","2008-02-02","Protocole de recherche",NULL,"0"
"1","21","2008-02-02","Si protocole : Nom du protocole","protocle 1","1"
"1","22","2008-02-02","Information/Consentement","oui","1"
"2","11","2007-07-07","IDENTITE",NULL,"0"
"2","12","2007-07-07","Numéro de dossier appareil","EFR2","1"
"2","13","2007-07-07","Date de l'examen","13/12/2005","1"
"2","14","2007-07-07","Heure de l'examen","10:30","1"
"2","16","2007-07-07","Date de naissance","22/07/1977","1"
"2","17","2007-07-07","TABAC",NULL,"0"
"2","19","2007-07-07","fumeur","oui","1"
"2","20","2007-07-07","Protocole de recherche",NULL,"0"
"2","21","2007-07-07","Si protocole : Nom du protocole","protocole 2","1"
"2","22","2007-07-07","Information/Consentement","non","1"

après ma requête je veux avoir:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
NIPATIENT  NIQ  DATE ENTREE  LIBABR  REPONSE  TYPE 
"1","11","2008-02-02","IDENTITE",NULL,"0"
"1","12","2008-02-02","Numéro de dossier appareil","EFR1","1"
"1","13","2008-02-02","Date de l'examen","21/03/2004","1"
"1","15","2008-02-02","Heure de l'examen","12:30","1"
"1","16","2008-02-02","Date de naissance","21/07/1977","1"
"1","17","2008-02-02","TABAC",NULL,"0"
"1","18","2008-02-02","test",NULL,"0"
"1","19","2008-02-02","fumeur","non","1"
"1","20","2008-02-02","Protocole de recherche",NULL,"0"
"1","21","2008-02-02","Si protocole : Nom du protocole","protocle 1","1"
"1","22","2008-02-02","Information/Consentement","oui","1"
"2","11","2007-07-07","IDENTITE",NULL,"0"
"2","12","2007-07-07","Numéro de dossier appareil","EFR2","1"
"2","13","2007-07-07","Date de l'examen","13/12/2005","1"
"2","14","2007-07-07","Heure de l'examen","10:30","1"
"2","16","2007-07-07","Date de naissance","22/07/1977","1"
"2","17","2007-07-07","TABAC",NULL,"0"
"2","18","2007-07-07","test",NULL,"0"
"2","19","2007-07-07","fumeur","oui","1"
"2","20","2007-07-07","Protocole de recherche",NULL,"0"
"2","21","2007-07-07","Si protocole : Nom du protocole","protocole 2","1"
"2","22","2007-07-07","Information/Consentement","non","1"

je n'ai pas le droit d'ajouter les élements dans la base.

merci encore pour votre aide.

31/03/2008, 10h25
miloux32

Code:

select `NIPATIENT`,"18",`DATE ENTREE`,"test",NULL,"0" from DUAL

C'est ca que tu veux ?

Merci pour votre aide, voilà ce que j'ai mis
Code:

1 2 3 4 5 SELECT * FROM `question` UNION SELECT `NIPATIENT` , "18", `DATE ENTREE` , "test", NULL , "0" FROM `question` ORDER BY 1 , 3, 2
j'obtiens exactement ce que je veux

merci encore

31/03/2008, 11h59
miloux32
Citation:
Envoyé par Smix007

Merci pour votre aide, voilà ce que j'ai mis

Code:

1 2 3 4 5 SELECT * FROM `question` UNION SELECT `NIPATIENT` , "18", `DATE ENTREE` , "test", NULL , "0" FROM `question` ORDER BY 1 , 3, 2

j'obtiens exactement ce que je veux

merci encore
Oui mais c'est tres tres tres moche ....

juste pour temontrer pourquoi :
fais sous ton requeteur
Code:

1 2 SELECT `NIPATIENT` , "18", `DATE ENTREE` , "test", NULL , "0" FROM `question`
Tu devrais avoir (en principe ) une ligne de resultat pour chaque ligne de ta table d'origine...chose que tu vois pas dans ton résultat car le union vire les doublons mais c'est tres tres couteux

Si toutes tes données de résultat sont en dur, fais ton select sur la table DUAL ....

Merci encore, mais ma requête permet d'afficher exactement ce que je veux et ce qui me facilite le code après:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
NIPATIENT  NIQ  DATE ENTREE  LIBABR  REPONSE  TYPE 
"1","11","2008-02-02","IDENTITE",NULL,"0"
"1","12","2008-02-02","Numéro de dossier appareil","EFR1","1"
"1","13","2008-02-02","Date de l'examen","21/03/2004","1"
"1","15","2008-02-02","Heure de l'examen","12:30","1"
"1","16","2008-02-02","Date de naissance","21/07/1977","1"
"1","17","2008-02-02","TABAC",NULL,"0"
"1","18","2008-02-02","test",NULL,"0"
"1","19","2008-02-02","fumeur","non","1"
"1","20","2008-02-02","Protocole de recherche",NULL,"0"
"1","21","2008-02-02","Si protocole : Nom du protocole","protocle 1","1"
"1","22","2008-02-02","Information/Consentement","oui","1"
"2","11","2007-07-07","IDENTITE",NULL,"0"
"2","12","2007-07-07","Numéro de dossier appareil","EFR2","1"
"2","13","2007-07-07","Date de l'examen","13/12/2005","1"
"2","14","2007-07-07","Heure de l'examen","10:30","1"
"2","16","2007-07-07","Date de naissance","22/07/1977","1"
"2","17","2007-07-07","TABAC",NULL,"0"
"2","18","2007-07-07","test",NULL,"0"
"2","19","2007-07-07","fumeur","oui","1"
"2","20","2007-07-07","Protocole de recherche",NULL,"0"
"2","21","2007-07-07","Si protocole : Nom du protocole","protocole 2","1"
"2","22","2007-07-07","Information/Consentement","non","1"

merci encore pour votre aide

Citation:

Envoyé par Smix007

Merci encore, mais ma requête permet d'afficher exactement ce que je veux et ce qui me facilite le code après:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
NIPATIENT  NIQ  DATE ENTREE  LIBABR  REPONSE  TYPE 
"1","11","2008-02-02","IDENTITE",NULL,"0"
"1","12","2008-02-02","Numéro de dossier appareil","EFR1","1"
"1","13","2008-02-02","Date de l'examen","21/03/2004","1"
"1","15","2008-02-02","Heure de l'examen","12:30","1"
"1","16","2008-02-02","Date de naissance","21/07/1977","1"
"1","17","2008-02-02","TABAC",NULL,"0"
"1","18","2008-02-02","test",NULL,"0"
"1","19","2008-02-02","fumeur","non","1"
"1","20","2008-02-02","Protocole de recherche",NULL,"0"
"1","21","2008-02-02","Si protocole : Nom du protocole","protocle 1","1"
"1","22","2008-02-02","Information/Consentement","oui","1"
"2","11","2007-07-07","IDENTITE",NULL,"0"
"2","12","2007-07-07","Numéro de dossier appareil","EFR2","1"
"2","13","2007-07-07","Date de l'examen","13/12/2005","1"
"2","14","2007-07-07","Heure de l'examen","10:30","1"
"2","16","2007-07-07","Date de naissance","22/07/1977","1"
"2","17","2007-07-07","TABAC",NULL,"0"
"2","18","2007-07-07","test",NULL,"0"
"2","19","2007-07-07","fumeur","oui","1"
"2","20","2007-07-07","Protocole de recherche",NULL,"0"
"2","21","2007-07-07","Si protocole : Nom du protocole","protocole 2","1"
"2","22","2007-07-07","Information/Consentement","non","1"

merci encore pour votre aide

Essaye ce que je t'ai dit : remplace le "question" de ta deuxieme requete par DUAL. Ca ne devrait rien changer a ton resultat en principe et ca devrait éviter de mettre X doublons qu'il virera apres.

31/03/2008, 16h44
Smix007
merci encore.
malheureusement MySql considére DUAL comme si c'était une table
Code:

1 2 MySQL a répondu: #1146 - La table 'terminologydb.dual' n'existe pas
31/03/2008, 18h03
SQLpro

DUAL est spécifique à Oracle. Mais vous pouvez créer votre propre table dual en créant un etable d'une seule colonnes avec une seule ligne :
CREATE TABLE DUAL (C INT);
INSERT INTO DUAL VALUES (1);

A +
01/04/2008, 11h02
Smix007

merci encore