JOIN Problem

[rajkum]

Hi,

I am not being able to figure out how to SQL my requirement.
Any help would be greatly appreciated.



Table 1:
--------
codeID -PK
codeNum -PK
title
dept_cd
status_cd
---------

Table 2History of Table 1)
Note : CodeNum in Table 1 could change. It is when this change occurs that the record moves to history Table.
--------
codeID -PK
codeNum -PK
title
dept_cd
status_cd
---------


Table 3:
--------
codeNew -PK
codeID
codeNum
year
type_cd
..
..
--------

Table 4: (code table)
-------
dept_cd -PK
dept_nm
------

Table 5: (code table)
-------
type_cd -PK
type_nm
------

Most of the data I am interested resides in Table 3.


Requirement:

Retrieve codeNew,codeID,codeNum,title FROM [TABLE 3] where year = '1991' and dept_cd = 1 and type = 'A'

(JOIN with both Table 1 and Table 2)



Thanks,
RajKum.

 

[amorous]

-i dont see a field named "Type"
-year is a key word, dont use as the field name

try something like this:

SELECT codeNew,codeID,codeNum,title FROM [TABLE 3] INNER JOIN [TABLE 1] ON [TABLE 1].CodeNum=[TABLE 3].codeNum INNER JOIN [TABLE 2] ON [TABLE 2].CodeNum=[TABLE 3].codeNum WHERE [TABLE 3].[year] = '1991' AND [TABLE 2].dept_cd = 1 AND [TABLE 1].type = 'A'

-VJ

 

[rajkum]

I am sorry .. but I missed a table here..
Let me re-write the requirement ..
Also I am incorporating suggestions by Amorous.


Table 1:
--------
codeID -PK
codeNum -PK
title
status_cd
---------

Table 2History of Table 1)
Note : CodeNum in Table 1 could change. It is when this change occurs that the record moves to history Table.
--------
codeID -PK
codeNum -PK
title
status_cd
---------


Table 3:
--------
codeNew -PK
codeID
codeNum
some_year
type_cd
..
..
--------




Table 4: (code table)
-------
dept_cd -PK
dept_nm
------

Table 5: (code table)
-------
type_cd -PK
type_nm
------

Table 6: (ASSOCIATION Table)
(This association is with Table 1 and table 2)
-------
codeID
codeNum
dept_cd
-------




Most of the data I am interested resides in Table 3.


Requirement:

Retrieve codeNew,codeID,codeNum,title FROM [TABLE 3] where year = '1991' and dept_cd = 1 and type_cd = 'A'

(JOIN with both Table 1 and Table 2)



Thanks,
RajKum.