Sqlite View : Add a column based on some other column

Question

Hi,

I have two tables

Employee

ID | Name | Department
----------------------
121  |Name1  | dep1
223  |Name2  | dep2

Assignment

ID | EID| 
---------
1  |121
2  |223
3  |121

[other columns omitted for brevity]

The table assignment indicates which is work is assigned to whom.EID is a foriegn key to the table Employee.Also it is possible to have two work assigned to the same employee.

Now i want to create a view like this

EID | Assigned
--------------
121 |true
333 |false

Assigned column should be calculated based on the entries in the Assignment table.

So far i am only successful in creating a view like this

EID | Assigned
--------------
121 |2
333 |0

using the command

CREATE VIEW "AssignmentView" AS
SELECT distinct ID ,(select Count(*)
from Assignment where Assignment.EID = Employee.ID) as Assigned
FROM  Employee;

Thanks

Answer 1

The CASE expression and a GROUP BY should do the trick:

Select
  e.id,
  Case When Count(a.eid) > 0 Then 'true' Else 'false' End As assigned
From employee e
Left Join assignment a On ( a.eid = e.id )
Group By e.id;