mysql - How to write a query that joins 3 tables -
i having issues query utilizes 3 different tables. believe using key words not exists , not within not positive on this. post query definition, tables using, , have tried far. in advance!
query:
find users create jobs utilize proteins.
tables:
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));
what ive tried far...
select u.uid, j.jobid, count(j.jobid) job j inner join job_proteins p on j.jobid = p.jobid inner join user u on p.uid = u.uid group j.jobid having count(j.jobid) = (select count(pid) protein);
does have jobs utilize every protein?.
missing member in group clause:
select u.uid, j.jobid, count(j.jobid) job j inner join job_proteins p on j.jobid = p.jobid inner join user u on p.uid = j.uid -- , had wrong alias here group u.uid, j.jobid -- right here having count(j.jobid) = (select count(pid) protein);