How to join a two tables compare with date?

1
votes

Using Access 2003

Table1

Personid Date1

101     02-02-2008
102     02-02-2008
103     02-02-2008
101     03-02-2008
102     03-02-2008
103     03-02-2008
101     04-02-2008
102     04-02-2008
103     04-02-2008

So On…,

Table2

Personid Date1 Name Title

101      03-02-2008 Raja Accountant
102      04-02-2008 Ravi Supervisor
103      02-02-2008 Ram Manager

So on…,

I want to display all personid, date1 from table1 and name, title from table 2 where table2.date1 = table1.date

Expected Output:

Personid Date1 Name Title

101     02-02-2008  
102     02-02-2008
103     02-02-2008 Ram Manager
101     03-02-2008 Raja Accountant
102     03-02-2008 
103     03-02-2008
101     04-02-2008 
102     04-02-2008 Ravi Supervisor
103     04-02-2008

So on…,

How to make a query for the above expected output.

Need Query Help

2
ms-access

2 Answers

4
votes
SELECT 
    Table1.personid, 
    Table1.date1, 
    Table2.name, 
    Table2.title
FROM Table1 
    LEFT JOIN Table2 ON Table1.date1 = Table2.date1
        AND Table1.personid = Table2.personid;

Similar to mik's, but the AND at the end there is necessary, otherwise it applies the name and title to rows it doesn't belong in

0
votes
SELECT Table1.personid, Table1.date1, Table2.name, Table2.title
FROM Table1 LEFT JOIN Table2 ON Table1.date1=Table2.date1;

This query should work