如何在MySQL中使用join

Question

In MySQL, I have two tables.

tasks

id

name

checklist

id

member_id

tasks_id

In the php page, I create the sql select string, I have a php variable that has a members_id value which is the member id of the person logged in. How can I get all the records of the tasks table, and add a new column to it called completed, and the value is true if there exists a record in the checklist table that has the member_id the same as the members id php variable and it's tasks_id value is the same as the id of the tasks table, and false if it doesn't exist?

Thanks

Answer 1

What you want is a left outer join:

select t.*, (cl.id is not null) as IsCompleted
from tasks t left outer join
     checklist cl
     on t.id = cl.taskid and
        cl.memberid = <your member id goes here>

The expression (cl.id is not null) returns true when there is a record in the checklist table and false otherwise.