Making table Join Problem?

Question

Using SQL Server 2000

Having Two Tables

Table1

Date ID

20090101 001
20090102 001
….
20090101 002
20090102 002
…

20090101 003
20090102 003
…

So on..,

Table2

ID Name Date

001 Raja 20090408
001 Raja 20090502
001 Raja 20090503
002 Ravi 20090312
002 Ravi 20090522
003 Saga 20090802
003 Saga 20091022

So on..,

Query

SELECT
   Table1.Date, Table1.ID, 
   Table2.Name, Table2.Date 
FROM table1 
INNER JOIN table2 ON table1.id = table2.id 
ORDER BY table1.id, table1.date

Output

Date ID Name Table2.Date

20090101 001 Raja 20090408
20090101 001 Raja 20090502
20090101 001 Raja 20090503
20090102 001 Raja 20090408
20090102 001 Raja 20090502
20090102 001 Raja 20090503
…

20090101 002 Ravi 20090312
20090101 002 Ravi 20090522
20090102 002 Ravi 20090312
20090102 002 Ravi 20090522
…

So on…,

Why am getting a table1.date again and again. I want to display like this.

Expected Output

Date ID Name Table2.Date

20090101 001 Raja   - 
…
20090502 001 Raja 20090502
20090503 001 Raja 20090503
…
20090107 001 Raja   -
20090108 001 Raja 20090408
….

So on…,

I put "–" instead of Null Column

Table2.Date should equal to Table1.Date, means 
Table1.Date, Table1.ID should display all the columns, 
Table2.Date should display with Table1.Date Related column.

Example.

Table2 Column Values are

Date - 20090203, 20090205...,

Table1.ID Table1.Date Table2.Date

001 20090201 
001 20090202 
001 20090203 20090203
001 20090204
001 20090205 20090205
001 20090206 

...,

Before I make a query in Access 2003

SELECT AllPossibleCardEvents.Id,  AllPossibleCardEvents.Date, Actual.Date AS Table2Date FROM ((SELECT p.Id,  AllDates.Date FROM (SELECT DISTINCT Date FROM table2) AllDates, table1  p) AllPossibleCardEvents LEFT OUTER JOIN table2  Actual ON AllPossibleCardEvents.Id = Actual.Id AND AllPossibleCardEvents.tDate = Actual.Date) )

Now the above query is working perfectly for my expected output in access

How to make a Sql query for this condition?

Need Query Help

Answer 1

By the looks of it, all you are missing is to join the date columns as well:

Select 
  Table1.Date, Table1.ID, Table2.Name, Table2.Date 
from table1 
inner join table2 on table1.id = table2.id and table1.date = table2.date
order by table1.id, table1.date

This is if I understood your question correctly.

If you want to display the NULL values, you need to change to a LEFT join though:

Select 
  Table1.Date, Table1.ID, Table2.Name, Table2.Date 
from table1 
left join table2 on table1.id = table2.id and table1.date = table2.date
order by table1.id, table1.date

EXTRA:

I've added some extra sql that might give you equivalent results to your Access query. If I understand the Access query correctly, you are matching up all records, but only displaying table2.Date when it matches up with table1.Date.

Try this:

SELECT
    table1.Id,
    table1.Date,
    CASE WHEN table1.Date = table2.Date THEN table2.Date ELSE '-' END AS Table2Date
FROM table1
CROSS JOIN table2

If this is still not what you are looking for, I don't think I understand your questions at all.

Answer 2

SELECT AllPossibleCardEvents.Date, 
       AllPossibleCardEvents.ID, 
       COALESCE(Table2.Date, '-') Table2Date
FROM (SELECT DISTINCT t1.ID, t2.Date 
        FROM table1 t1 CROSS JOIN table2 t2) AllPossibleCardEvents
     LEFT JOIN table2 ON AllPossibleCardEvents.ID = table2.ID 
                     AND AllPossibleCardEvents.Date = table2.Date
ORDER BY AllPossibleCardEvents.ID, AllPossibleCardEvents.Date

Answer 3

First of all, if you have non-existing values in table2, you need to use LEFT OUTER JOIN instead of INNER JOIN. The INNER JOIN by design will only shows rows where both tables have values, e.g. it will filter out anything that doesn't exist in table2. Also, you're not joining on the "date" - that's why you get so many more result rows than expected.

Second, if you want to display something else than NULL for the non-existing values in table2, you need to use ISNULL to specify what to display instead of NULL:

SELECT
   Table1.Date, Table1.ID, Table2.Date
FROM table1 
LEFT OUTER JOIN 
   table2 ON table1.id = table2.id AND table1.date = table2.date
ORDER BY table1.id, table1.date

I get output something like this:

table1.Date                 table1.ID    table2.Date
2009-01-01 00:00:00.000        1            NULL
2009-01-02 00:00:00.000        1            NULL
2009-02-03 00:00:00.000        1            NULL
2009-02-05 00:00:00.000        1            NULL
2009-04-08 00:00:00.000        1         2009-04-08 00:00:00.000
2009-05-03 00:00:00.000        1         2009-05-03 00:00:00.000
2009-01-01 00:00:00.000        2            NULL
2009-01-02 00:00:00.000        2            NULL
2009-01-01 00:00:00.000        3            NULL
2009-01-02 00:00:00.000        3            NULL

Please clarify what is not good with this output....

Marc