Join Tables to return 1 or 0 based on multiple conditions

I am working on a project management website and have been asked for a new feature in a review meeting section.

A meeting is held to determine whether to proceed to the next phase, and I need to maintain a list of who attended each phase review meeting. I need to write an SQL query to return all people, with an additional column that states they have already been added before.

There are two tables involved to get my desired result, with the relevant columns listed below:

Name: PersonList
ID | Name | Division 

Name: reviewParticipants
ProjectID | PersonID | GateID

The query I am looking for is something that returns all people in PersonList, with an additional “hasAttended” bit that is TRUE if reviewParticipants.ProjectID = 5 AND reviewParticpants.CurrentPhase = ‘G0’ ELSE FALSE.

PersonName | PersonID | hasAttended
Mr Smith | 1 | 1
Mr Jones | 2 | 0

I am not sure how to structure such a query with multiple conditions in a (left?) join, that would return as a different column name and data type, so I would appreciate if anybody can point me in the right direction?

With the result of this query I am going to add a series of checkboxes, and use this additional bit to mark it checked, or not, for page refreshes.

Answer

You can use LEFT JOIN as well:

SELECT DISTINCT p.*
      ,CASE WHEN rp.id IS NOT NULL THEN 1 ELSE 0 END AS hasAttended
  FROM personlist p
  LEFT JOIN reviewParticipants rp ON rp.personid = p.id
                                  AND rp.projectid = 5
                                  AND rp.currentphase = 'GO'

I agree with Gordon Linoff: I would prefer an int or tinyint over a bit value,