Problem creating a view

[arnelmarcelo]

How can i do this

I have Document Table with the following fields
Document.iid Integer (primary key)
document.name Character

I have another table with the following fields
DocuData.iid integer (primary key)
Docudata.empid integer
docudata.docid integer (linked to document.iid)
docudata.docnumber C(20)
docudata.dateissued date
docudata.dateexpiry date

I want to get a view so that all records on Document table are included and any matching docudata fields with a given empid. Below is my sql statement. However the result is that only docudata with empid = to the required value are shown. How can I force the SQL statement to always include all the documents data. Say if there are 10 records in the documents data those are always shown regardless if there is no matching DOCUDATA record. say something like this: Say if vp_empid = 2

PRC License , 12345 , 10/21/2010 , 10/21/2011 , 1 , 2 , 1 , 1
Drivers License, 12383 , 10/01/2009 , 10/01/2014 , 2 , 2 , 2 , 2
SSS , .null. , .null. , .null. , .null. , .null. , .null. , 3
GSIS , .null. , .null. , .null. , .null. , .null. , .null. , 4

SELECT Documents.description, Docudata.docnumber, Docudata.dateissued,;
Docudata.dateexpiry, Docudata.iid, Docudata.empid, Docudata.docid;
FROM ;
ispacct!docudata ;
LEFT OUTER JOIN ispacct!documents ;
ON Docudata.docid = Documents.iid;
WHERE Docudata.empid = ( ?vp_empid )


the problem with the above statement is that only the first two records appear. Can anybody help me to get the desired results. I already tried different combinations of joins and filter but still I can't get the desired results. The above statement will only work if there is a matching employee document in the docudata table but not if there is not a one to one match.

I can do this if i use two views. However I want to do this with a single SELECT statement. Can any body help please.

 

[Olaf Doschke]

The solution is easy. If you don't want a condition to be generally fullfilled, but just for the join, you add it to the join condition.

SELECT Documents.description, Docudata.docnumber, Docudata.dateissued,;
Docudata.dateexpiry, Docudata.iid, Docudata.empid, Docudata.docid;
FROM ;
ispacct!docudata ;
LEFT OUTER JOIN ispacct!documents ;
ON Docudata.docid = Documents.iid AND Docudata.empid = ( ?vp_empid )

Condition put into where are applied to the whole recordset with already joined records, And in cases there is no join the joined fields are NULL, NULL will only fulfill where conditions testing for NULL, but nothing like Docudata.empid = ( ?vp_empid , so you put it in the join condition and thereby only join docudata a) belonging to the document and b) having the correct emp_id.

Bye, Olaf.