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.

Answers


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;

Need Your Help

How to enable html content on top of flash content without using the “transparent” wmode parameter or how to optimize framerate when using the “transparent” wmode parameter for a flash object?

html css flash actionscript-3 z-index

I'm developing a Flash application which is communicating with Javascript to allow more features such as custom html input etc., by placing an absolute positioned div on top of the flash applicatio...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.