CODEWITHSUNDEEP
sqldatabasesqlitejoinselect-query
Remi
Remi

Reputation: 1299

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:

Table Document:

Table DocEmp:

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.

Upvotes: 0

Views: 40

Answers (1)

FutbolFan
FutbolFan

Reputation: 13723

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;

Upvotes: 2

Related Questions