SQL Union without duplicate values for one specific field

Question

There're 3 tables DEVELOPER, MANAGER and PROJECT:

create table DEVELOPER(id int(4), lastname varchar(40), project_id int(4));

create table MANAGER(id int(4), lastname varchar(40), project_id int(4));

create table PROJECT(id int(4), name varchar(40));

The relantionship between PROJECT and DEVELOPER is One-To-Many, and the relantionship between PROJECT and MANAGER is One-To-One.

All last names stored in the database are unique. The manager can be simultaneously a developer on the same project. (his data stored in two tables in this case).

I want to receive list of all the developers and manager on the specific project from my database in the following form:

id | lastname | isManager

I think it would suit the following query:

SELECT id, lastname, false AS isManager FROM developer WHERE project_id = 314
UNION 
SELECT id, lastname true AS isManager FROM manager WHERE project_id = 314 

But I do not want to have two row of manager's data if he is also the developer for the specific project! I want to leave the line where property isManager is true. Please suggest me proper query to resolve this problem!

Answer 1

You can utilize FULL OUTER JOIN for that purpose:

SELECT COALESCE(m.id, d.id) AS id  -- m comes first!
     , lastname                    -- lastname consolidated by USING
     , CASE WHEN m.id IS NOT NULL THEN TRUE ELSE FALSE END AS is_manager
FROM   developer d
FULL   OUTER JOIN manager m USING (lastname, project_id)
WHERE  project_id = 314;

But first you might consider @a_horse_with_no_name's advice about your db design.