How to display a non matching value also?

Question

USING VB6 AND MS-ACCESS 2003

So on…,

TABLE 1

EMPID DATE     

101 22-07-2009 
201 22-07-2009 
501 22-07-2009  
301 23-07-2009
401 23-07-2009
501 23-07-2009
101 24-07-2009
501 24-07-2009

So on…,

From the above table two tables I want to display all EMP ids for the date wise

Expected Output

EMPID  DATE

101 22-07-2009
201 22-07-2009
301 
401 
501 22-07-2009
101
201
301 23-07-2009
401 23-07-2009
501 23-07-2009
101 24-07-2009
201
301
401
501 24-07-2009

So on…,

Need Query Help.

Answer 1

Read here on LEFT OUTER JOIN and other type of JOINs.

EDIT: I am not giving you the query knowingly. See the link above and write a query yourself.

Answer 2

Without questioning your data model, to get the results you want you will need a third table (which I will call Dates) You need a Cross Join on Table 1 and Dates, which will give a result of all employees for all days. Then you need to Left Join to EmpID and Date. The Left Join will include all of the results from the first join but only the matching rows from Table 2 will be populated. Access is funny in how it handles query structure, also it does not support the SQL-92 CROSS JOIN syntax, but it would look something like the below.

SELECT t1.EmpID, t2.Date 
  FROM (
        SELECT t1.EmpID, d.Date 
          FROM [Table 1] AS t1, 
               Dates AS d
       ) AS DT1
       LEFT OUTER JOIN [Table 2] AS t2 
          ON DT1.EmpID = t2.EmpID 
             AND DT1.Date = t2.Date
 ORDER 
    BY DT1.Date, DT1.EmpID;

Answer 3

Haven't executed to verify for sure, but this should get you most of the way there:

SELECT
    AllPossibleCardEvents.PersonId, 
    AllPossibleCardEvents.EmpName,
    AllPossibleCardEvents.TitleCode,
    AllPossibleCardEvents.TitleName,
    AllPossibleCardEvents.CardEventDate, 
    ActualCardEvents.CardEventDate AS MatchingCardEventDate
FROM
    (
     (
      SELECT
       p.PersonId, 
       p.EmpName,
       p.TitleCode,
       p.TitleName,
       AllDates.CardEventDate
      FROM
       (SELECT DISTINCT CardEventDate FROM T_Cardevent) AllDates, 
       T_Person p
     ) AllPossibleCardEvents
     LEFT OUTER JOIN T_Cardevent ActualCardEvents ON 
      AllPossibleCardEvents.PersonId = Actual.PersonId AND 
      AllPossibleCardEvents.CardEventDate = Actual.CardEventDate
    )

Where "MatchingCardEventDate" will be NULL for records that are NOT actual events. For actual events, the value of "MatchingCardEventDate" will be the valid date.

Hope this helps.