循环遍历父表中的记录，其中父表中的parentTableID和

I have 2 tables complaints and tasks.

The complaintID is unique in the complaints table i.e the parent table and the it is the foreign key in the task table.

So one complaint can have multiple tasks therefore in the task table you can have the same complaintID many times.

I want to display all fields from the complaints table when ALL related task have the status as complete

I have tried this but is does not work completely because if a complaint has 4 tasks and only 1 task is marked as complete then it pulls that complaint:

SELECT * FROM complaints WHERE complaintID IN 
(SELECT * FROM tasks WHERE completed="yes") ORDER BY deadline

I would grateful if someone could help me with this one.

You can do it like this also

select complaints.*, tasks.* from complaints, tasks where 
complaints.complaintID=tasks.complaintID and tasks.completed='yes' and 
complaints.complaintID NOT IN (select complaintID from tasks where completed='no')

The table Structure used was :

CREATE TABLE complaints (
  complaintID INT AUTO_INCREMENT PRIMARY KEY,
  content VARCHAR(255),
  deadline datetime
  );

CREATE TABLE tasks (
  tasksid INT AUTO_INCREMENT PRIMARY KEY,
  complaintID INT,
  completed ENUM ('yes','no')
  );

INSERT INTO complaints(content,deadline) VALUES ('complaint1', NOW()+INTERVAL 3 day), ('complaint2',NOW()+INTERVAL 8 day);

INSERT INTO tasks (complaintID, completed) VALUES(1,'no'), (1,'yes'), (2,'no'); 

I used the table structure from @Mr. Radical, from his SQL FIDDLE.

Hope it helps

select  *
from    complaints c
where   not exists
        (
        select  *
        from    tasks t
        where   t.ComplaintID = c.ComplaintID
                and t.Completed <> 'yes'
        )

You want to look up only the foreign key attribute on the 'inner' table. So, write it like this: SELECT * FROM complaints WHERE complaintID IN (SELECT complaintId* FROM tasks WHERE completed="yes") ORDER BY deadline * or whatever your foreign key is on the task table

EDITED

Try this:

 SELECT * FROM complaints 
 WHERE NOT EXISTS 
       (SELECT complaintid 
       FROM tasks 
       WHERE complaintid 
       in (SELECT complaintID 
           FROM tasks 
           WHERE tasks.complaintID = complaints.complaintid 
           and completed = 'no') 
       )ORDER BY deadline;

SQL FIDDLE DEMO

This is the table structure and sample data if have used to test my answer above.

CREATE TABLE complaints (
  complaintID INT AUTO_INCREMENT PRIMARY KEY,
  content VARCHAR(255),
  deadline datetime
  );

CREATE TABLE tasks (
  tasksid INT AUTO_INCREMENT PRIMARY KEY,
  complaintID INT,
  completed ENUM ('yes','no')
  );

INSERT INTO complaints(content,deadline) VALUES ('complaint1', NOW()+INTERVAL 3 day), ('complaint2',NOW()+INTERVAL 8 day),('complaint3', NOW()+INTERVAL 3 day);

INSERT INTO tasks (complaintID, completed) VALUES(1,'no'), (1,'yes'), (2,'yes'), (1,'no'), (1,'yes'),  (1,'yes'), (2,'no'), (3,'yes'), (3, 'yes');