问题描述
我想选择在表ASSIGNMENTS中链接的所有PROJECT_ID，STAFF_ID，HARDWARE_ID和SOFTWARE_ID，但我想显示该链接，同时还显示每个表中与相关ID链接的所有属性(例如STAFF_NAME，JOB_TYPE) ，STAB表中的JOB_GRADE).这是我的代码:
I want to SELECT PROJECT_ID, STAFF_ID, HARDWARE_ID and SOFTWARE_ID which are all linked in a table ASSIGNMENTS, but I want to show that link while also showing all the attributes in each respective table linked to the relevant ID (e.g STAFF_NAME, JOB_TYPE, JOB_GRADE from the STAFF table). Here is my code:
CREATE TABLE PROJECT
(PROJECT_ID CHAR(5) NOT NULL,
PROJECT_NAME CHAR(20),
PROJECT_TYPE CHAR(20),
START_DATE DATE,
END_DATE DATE,
PRIMARY KEY (PROJECT_ID));
CREATE TABLE HARDWARE
(HARDWARE_ID CHAR(5) NOT NULL,
HARDWARE_NAME CHAR(20),
PRIMARY KEY (HARDWARE_ID));
CREATE TABLE SOFTWARE
(SOFTWARE_ID CHAR(5) NOT NULL,
SOFTWARE_NAME CHAR(20),
PRIMARY KEY (SOFTWARE_ID));
CREATE TABLE STAFF
(STAFF_ID CHAR(5) NOT NULL,
STAFF_NAME CHAR(20),
JOB_TYPE CHAR(20),
JOB_GRADE CHAR(20),
PRIMARY KEY (STAFF_ID));
CREATE TABLE ASSIGNMENTS
(ASSIGNMENT_ID CHAR(5) NOT NULL,
PROJECT_ID CHAR(5),
STAFF_ID CHAR(5),
HARDWARE_ID CHAR(5),
SOFTWARE_ID CHAR(5),
PRIMARY KEY (ASSIGNMENT_ID),
FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID),
FOREIGN KEY (STAFF_ID) REFERENCES STAFF(STAFF_ID),
FOREIGN KEY (HARDWARE_ID) REFERENCES HARDWARE(HARDWARE_ID),
FOREIGN KEY (SOFTWARE_ID) REFERENCES SOFTWARE(SOFTWARE_ID));
以下是一些基本数据:
INSERT INTO PROJECT (PROJECT_ID, PROJECT_NAME, PROJECT_TYPE, START_DATE, END_DATE)
VALUES ('B0001','BIKESHOP.COM','WEB DEVELOPMENT',TO_DATE('15/01/17','DD/MM/YY'),TO_DATE('15/02/17','DD/MM/YY'));
INSERT INTO HARDWARE (HARDWARE_ID, HARDWARE_NAME)
VALUES ('H0001','WEBDEV PC1');
INSERT INTO SOFTWARE (SOFTWARE_ID, SOFTWARE_NAME)
VALUES ('S0001','COFFEECUP IDE');
INSERT INTO STAFF (STAFF_ID, STAFF_NAME, JOB_TYPE, JOB_GRADE)
VALUES ('ST001','JOHN MASON','WEB DEVELOPER','1');
INSERT INTO ASSIGNMENTS (ASSIGNMENT_ID, PROJECT_ID, STAFF_ID, HARDWARE_ID, SOFTWARE_ID)
VALUES ('A0001','B0001','ST001','H0001','S0001');
我已经使用此代码来获取PROJECT_ID，该PROJECT_ID也与STAFF_NAME链接到多个STAFF_ID值:
I have used this code to get a PROJECT_ID linked to multiple STAFF_ID values with STAFF_NAME also given:
SELECT STAFF.STAFF_ID, STAFF.STAFF_NAME, ASSIGNMENTS.PROJECT_ID
FROM ASSIGNMENTS
INNER JOIN STAFF ON ASSIGNMENTS.STAFF_ID = STAFF.STAFF_ID
WHERE ASSIGNMENTS.PROJECT_ID = 'B0001';
但是如何从链接到单个PROJECT_ID的所有表中选择所有属性?
But how can I SELECT all attributes from all tables linked to a single PROJECT_ID?
推荐答案
像这样的大连接:
Select s.staff_name,
H.Hardware_name,
Sf.Software_name,
P.Project_name,
P.Project_type --you get the idea. Select other columns if you need
From assignments a
Inner join staff s on a.staff_id = s.staff_id
Inner join software sf on a.software_id = sf.software_id
Inner join hardware h on a.hardware_id = h.hardware_id
Inner join project p on a.project_id = p.project_id
Where a.project_id = 'A0001';
这篇关于如何从5个表中选择多个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！