mysql - 如何编写连接 3 个表的查询

Question

我在使用三个不同表的查询时遇到问题。我也相信我会使用关键词“不存在”和“不在内部”，但我对此并不完全肯定。我将发布查询定义、我正在使用的表以及我到目前为止所尝试的内容。感谢您提前的帮助!

查询:

Find the users that create jobs that utilize all proteins.

表格:

CREATE TABLE Protein(pid INTEGER,name varchar(50),PRIMARY KEY(pid));

CREATE TABLE Job(uid INTEGER,job_id INTEGER AUTO_INCREMENT,input 
varchar(500),status varchar(100),start_time time,finish_time time,FOREIGN 
KEY(uid) REFERENCES User(uid) ON DELETE CASCADE,PRIMARY KEY(job_id));

CREATE TABLE User(uid INTEGER AUTO_INCREMENT,address VARCHAR(40),city 
VARCHAR(20),state VARCHAR(20),zipcode VARCHAR(10), username 
VARCHAR(10),email VARCHAR(30),primary key (uid));

CREATE TABLE job_protein(job_id INTEGER, pid INTEGER, PRIMARY KEY(job_id, pid));

到目前为止我尝试过...

SELECT u.uid, j.jobid, count(j.jobid)
FROM job j
INNER JOIN job_proteins p
ON j.jobid = p.jobid
INNER JOIN user u
ON p.uid = u.uid
GROUP BY j.jobid
HAVING count(j.jobid) = (SELECT count(pid) FROM Protein);

我所拥有的能找到利用每种蛋白质的工作吗？

Answer 1

您的 group by 子句中缺少成员:

SELECT u.uid, j.jobid, count(j.jobid)
FROM job j
INNER JOIN job_proteins p
ON j.jobid = p.jobid
INNER JOIN user u
ON p.uid = j.uid -- and you had wrong alias here
GROUP BY u.uid, j.jobid -- right here
HAVING count(j.jobid) = (SELECT count(pid) FROM Protein);