Tricky Inner Join

[Maik Richter]

Hi guys,

I need to join 2 tables with 2 criterias

i.e.:
The 1st key is the document number
and after the 1st key matches there should
be a 2nd check, i.e. the year should be checked.

I got no idea how to bring that into SQL.

Example:

Table1:
Row 10: DocNumber: 1 / Year: 2004
Row 20: DocNumber: 2 / Year: 2000

Table2:
Row 100: DocNumber: 1 / Year: 2001
Row 200: DocNumber. 2/ Year: 2000

So ONLY DocNumber 2 should match as
there is a different Year in DocNumber 1.


Here is my join so far:

FROM [Table1] INNER JOIN [DocNumber] ON
[Table1].[DocNumber]=[Table2].[DocNumber]



How write the SQL Command..I´m really lost..


Thanks a million..
MAIK

 

[Douglas J. Steele]

You've got an error in what you already have. It should be

FROM [Table1] INNER JOIN [Table2] ON
[Table1].[DocNumber]=[Table2].[DocNumber]

To include the year, make it

FROM [Table1] INNER JOIN [Table2] ON
[Table1].[DocNumber]=[Table2].[DocNumber]
AND [Table1].[Year]=[Table2].[Year]

(BTW, Year isn't a particular good name for a field. It's a reserved word,
so can cause problems in certain circumstances.)