, Guest>> || || |
/ Microsoft SQL Server
    [new]
-
Member

:
: 10
, , , !

:

CREATE TABLE PATIENTS
(
PATIENT_ID INTEGER NOT NULL,
SURNAME_P Nchar(100),
NAME_P Nchar(100),
MIDDLE_P Nchar(100),
BIRTHDAY_P DATETIME,
ADDRESS_P Nchar(500),
TELEFON_P NUMERIC(10)
);

CREATE UNIQUE INDEX IXPATIENTS ON PATIENTS
(
PATIENT_ID ASC
);

CREATE TABLE DIAGNOSIS
(
DIAGNOSIS_ID INTEGER NOT NULL,
NAME_DIAG Nchar(100)
);

CREATE UNIQUE INDEX IXDIAGNOSIS ON DIAGNOSIS
(
DIAGNOSIS_ID ASC
);

:, :
CREATE TABLE PAT_DIAG
(
PATIENT_ID INTEGER NOT NULL,
DIAGNOSIS_ID INTEGER NOT NULL
);

CREATE UNIQUE INDEX IXPAT_DIAG ON PAT_DIAG
(
PATIENT_ID ASC,
DIAGNOSIS_ID ASC
);

:
ALTER TABLE PATIENTS
ADD PRIMARY KEY (PATIENT_ID);

ALTER TABLE DIAGNOSIS
ADD PRIMARY KEY (DIAGNOSIS_ID);

:

INSERT INTO PATIENTS VALUES (123, '', '', '', '14/12/1946', ' 4/1/108', 4964776523);
INSERT INTO PATIENTS VALUES (108, '', '', '', '13/08/1933', ' 7/18', 4996023764);
INSERT INTO PATIENTS VALUES (14, '', '', '', '03/04/1988', ' 18/1/65', 4954731674);
INSERT INTO PATIENTS VALUES (365, '', '', '', '05/01/1964', ' 32/1/43', 4994775423);
INSERT INTO PATIENTS VALUES (19, '', '', '', '06/08/1945', ' 12/198', 4954770907);
INSERT INTO PATIENTS VALUES (186, '', '', '', '29/04/1936', ' 17/1/309', 4995608265);
INSERT INTO PATIENTS VALUES (204, '', '', '', '19/12/1990', ' 18/2/17', 4957092267);
INSERT INTO PATIENTS VALUES (104, '', '', '', '21/03/1970', ' 17/1/108', 4994785593);
INSERT INTO PATIENTS VALUES (226, '', '', '', '26/01/1950', ' 1/88', 4996075299);
INSERT INTO PATIENTS VALUES (5, '', '', '', '09/04/1972', ' 1/1/205', 4995529845);
INSERT INTO PATIENTS VALUES (102, '', '', '', '18/11/1951', ' 6/303', 4952276603);
INSERT INTO PATIENTS VALUES (413, '', '', '', '14/10/1985', ' 38/189', 4994701698);
INSERT INTO PATIENTS VALUES (11, '','', '', '18/01/1978', ' 25/3/33', 4956085538);
INSERT INTO PATIENTS VALUES (199, '', '', '', '06/09/1957', ' 1/306', 4990573405);
INSERT INTO PATIENTS VALUES (58, '', '', '', '04/11/1979', ' 4/89', 4998096643);
INSERT INTO PATIENTS VALUES (13, '', '', '', '19/09/1967', ' 12/3/194', 4957093754);
INSERT INTO PATIENTS VALUES (276, '', '', '', '28/02/1966', ' 1/10', 4992436674);
INSERT INTO PATIENTS VALUES (7, '', '', '', '04/07/1945', ' 4/109', 4954722784);
INSERT INTO PATIENTS VALUES (288, '', '', '', '07/04/1989', ' 1/34', 4992430946);
INSERT INTO PATIENTS VALUES (87, '', '', '', '02/07/1978', ' 6/3/11', 4952438504);

INSERT INTO DIAGNOSIS VALUES (34, '');
INSERT INTO DIAGNOSIS VALUES (22, ' ');
INSERT INTO DIAGNOSIS VALUES (17, ' ');
INSERT INTO DIAGNOSIS VALUES (3, '');
INSERT INTO DIAGNOSIS VALUES (40, '');
INSERT INTO DIAGNOSIS VALUES (19, '');
INSERT INTO DIAGNOSIS VALUES (25, '');
INSERT INTO DIAGNOSIS VALUES (51, '');

:
ALTER TABLE PAT_DIAG
ADD FOREIGN KEY (PATIENT_ID) REFERENCES PATIENTS;

ALTER TABLE PAT_DIAG
ADD FOREIGN KEY (DIAGNOSIS_ID) REFERENCES DIAGNOSIS;

, , : , .

SELECT PATIENTS.SURNAME_P, PATIENTS.NAME_P, PATIENTS.MIDDLE_P, DIAGNOSIS.NAME_DIAG
FROM PATIENTS, DIAGNOSIS
WHERE PATIENTS.PATIENT_ID=PAT_DIAG.PATIENT_ID AND PAT_DIAG.DIAGNOSIS_ID=DIAGNOSIS.DIAGNOSIS_ID
ORDER BY PATIENTS.SURNAME_P;

:
4104, 16, 1, 3
"PAT_DIAG.PATIENT_ID".
4104, 16, 1, 3
"PAT_DIAG.DIAGNOSIS_ID".

, ?...
20 13, 13:47[14072013]      |
 Re:   [new]
-
Member

:
: 10
, , PAT_DIAG :

INSERT INTO PAT_DIAG VALUES (123, 34);
INSERT INTO PAT_DIAG VALUES (108, 22);
INSERT INTO PAT_DIAG VALUES (14, 17);
INSERT INTO PAT_DIAG VALUES (365, 3);
INSERT INTO PAT_DIAG VALUES (19, 40);
INSERT INTO PAT_DIAG VALUES (186, 22);
INSERT INTO PAT_DIAG VALUES (204, 3);
INSERT INTO PAT_DIAG VALUES (104, 22);
INSERT INTO PAT_DIAG VALUES (226, 40);
INSERT INTO PAT_DIAG VALUES (5, 19);
INSERT INTO PAT_DIAG VALUES (102, 17);
INSERT INTO PAT_DIAG VALUES (413, 25);
INSERT INTO PAT_DIAG VALUES (11, 51);
INSERT INTO PAT_DIAG VALUES (199, 3);
INSERT INTO PAT_DIAG VALUES (58, 34);
INSERT INTO PAT_DIAG VALUES (13, 40);
INSERT INTO PAT_DIAG VALUES (276, 25);
INSERT INTO PAT_DIAG VALUES (7, 25);
INSERT INTO PAT_DIAG VALUES (288, 51);
INSERT INTO PAT_DIAG VALUES (87, 17);
20 13, 13:50[14072032]      |
 Re:   [new]

Member

:
: 22550
FROM,

,
20 13, 13:54[14072057]      |
 Re:   [new]
PaulYoung
Member

:
: 2567
-,

,
SELECT PATIENTS.SURNAME_P, PATIENTS.NAME_P, PATIENTS.MIDDLE_P, DIAGNOSIS.NAME_DIAG
FROM PATIENTS
JOIN PAT_DIAG ON PATIENTS.PATIENT_ID=PAT_DIAG.PATIENT_ID
JOIN DIAGNOSIS ON PAT_DIAG.DIAGNOSIS_ID=DIAGNOSIS.DIAGNOSIS_ID
ORDER BY PATIENTS.SURNAME_P
20 13, 13:58[14072091]      |
 Re:   [new]
-
Member

:
: 10
,

FROM, :
4104, 16, 1, 3
"PATIENTS.PATIENT_ID".
4104, 16, 1, 3
"PAT_DIAG.PATIENT_ID".
4104, 16, 1, 3
"PAT_DIAG.DIAGNOSIS_ID".
207, 16, 1, 3
"DIAGNOSIS_ID".
4104, 16, 1, 1
"PATIENTS.SURNAME_P".
4104, 16, 1, 1
"PATIENTS.NAME_P".
4104, 16, 1, 1
"PATIENTS.MIDDLE_P".
207, 16, 1, 1
"NAME_DIAG".
4104, 16, 1, 4
"PATIENTS.SURNAME_P".

... IDEF1X, .
20 13, 13:59[14072096]      |
 Re:   [new]
-
Member

:
: 10
PaulYoung,

, ! ... , :)
20 13, 14:00[14072104]      |
/ Microsoft SQL Server