Problème jointure mysql

**snorky94** · 17/10/2012, 13h19

Bonjour,

Encore un pb de jointure

Avec cette requete :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT
ENV.NAME_ENV,ENV.ID_ENV,T1.NAME_SERVER
FROM
ENV
left join
(SELECT  SERVER.NAME_SERVER , HOST.ID_ENV FROM HOST,SERVER
WHERE  HOST.ID_SERVER=SERVER.ID_SERVER
AND SERVER.NAME_SERVER ='TITI'
) AS T1
on
ENV.ID_ENV = T1.ID_ENV;

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
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
 
CREATE TABLE SERVER (
ID_SERVER INT NOT NULL AUTO_INCREMENT,
DATE_SERVER TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
IP_REQ_SERVER VARCHAR(15),
STATUS_SERVER VARCHAR(50),
NAME_SERVER  VARCHAR(50) NOT NULL,
DESC_SERVER VARCHAR(1000),
COM_SERVER VARCHAR(1000),
PRIMARY KEY (ID_SERVER),
UNIQUE KEY (NAME_SERVER)
); 
 
CREATE TABLE APP (
ID_APP INT NOT NULL AUTO_INCREMENT,
DATE_APP TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
IP_REQ_APP VARCHAR(15),
STATUS_APP VARCHAR(50),
NAME_APP VARCHAR(50) NOT NULL,
DESC_APP VARCHAR(1000),
COM_APP VARCHAR(1000),
DOC_APP VARCHAR(1000),
CODE_APP VARCHAR(1000),
PRIMARY KEY (ID_APP),
UNIQUE KEY (NAME_APP)
); 
 
CREATE TABLE RPA (
ID_RPA INT NOT NULL AUTO_INCREMENT,
DATE_RPA TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
IP_REQ_RPA VARCHAR(15),
STATUS_RPA VARCHAR(50),
NAME_RPA VARCHAR(50) NOT NULL,
COM_RPA VARCHAR(1000),
PRIMARY KEY (ID_RPA),
UNIQUE KEY (NAME_RPA)
); 
 
CREATE TABLE ENV (
ID_ENV INT NOT NULL AUTO_INCREMENT,
DATE_ENV TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
IP_REQ_ENV VARCHAR(15),
STATUS_ENV VARCHAR(50),
NAME_ENV VARCHAR(50) NOT NULL,
DESC_ENV VARCHAR(1000),
COM_ENV VARCHAR(1000),
PRIMARY KEY (ID_ENV),
UNIQUE KEY (NAME_ENV)
); 
 
CREATE TABLE HOST (
ID_APP INT NOT NULL,
ID_ENV INT NOT NULL,
ID_SERVER INT NOT NULL,
FOREIGN KEY (ID_APP) REFERENCES APP(ID_APP),
FOREIGN KEY (ID_ENV) REFERENCES ENV(ID_ENV),
FOREIGN KEY (ID_SERVER) REFERENCES SERVER(ID_SERVER),
PRIMARY KEY (ID_APP, ID_ENV, ID_SERVER)
); 
 
CREATE TABLE RESPONSIBLE (
ID_APP INT NOT NULL,
ID_RPA INT NOT NULL,
FOREIGN KEY (ID_APP) REFERENCES APP(ID_APP),
FOREIGN KEY (ID_RPA) REFERENCES RPA(ID_RPA),
PRIMARY KEY (ID_APP, ID_RPA)
); 
 
 
CREATE TABLE USER (
ID_USER INT NOT NULL AUTO_INCREMENT,
LOGIN_USER VARCHAR(50) NOT NULL,
PWD_USER VARCHAR(50) NOT NULL,
PRIMARY KEY (ID_USER)
);