How to join my tables?

I'm using a Sqlite3 database and I have 3 tables which I want to join. My program basically has documents. And you can select multiple employees that belong to the document. But employees can also belong to other documents.

Below are my tables:

Table Employee:

id:Integer primary key

Table Document:

id:Integer primary key

Table DocEmp:

id:Integer primary key
docId:Integer
empId:Integer
state:Integer

The DocEmp table can be empty only the Employee and Document table is always filled. Most important is that i always want to load all employees!

So for example: I have 3 employees with id's: 1,2 and 3. Have 2 documents with id 1 and 2. The DocEmp has the following records: Row1: 1,1,1,0 Row2: 2,1,2,1 So this means only document 1 has 2 employees with id 1 and 2. So when i'm in document 1 i want to load all 3 employees, and also want to know the state of the 2 employees that are filled in for that document in the DocEmp table. And because document 2 has no employees i need just all the employees.

Solution

If I understand your question correctly, I think you might be looking for a left join:

select e.id as employeeid,
       d.id as documentid,
       de.state as state     
from employee e
--this could be an inner join but your example is confusing
left join document d on e.id = d.id 
left join docemp de on e.id = de.empid 
                   and d.id = de.docid;