Combining 3 tables using DataBase command

[paramsocool]

Hi

I have tables in SQL SERVER and the fields are

TABLE A ---POLICY_ID ( Primay Key)
POLICY_NUM

TABLE B- POLICY_ID ( F Key)
ISSUE_DATE
DELETE_DATE

TABLE C- POLICY_ID ( F KEY)
CANCEL DATE

Only those records are to be picked from TABLE A for which there is RECORD in TABLE B and TABLE C so in other words TABLE B and C's all records are to be picked and POLICY_NUM for the record picked from TABLE B or C is to be received from TABLE A
I am trying this logic but is not giving me the proper result
select
A.POLICY_NUM,
B.ISSUE_DATE,
B.DELETE_DATE,
C.CANCEL_DATE
from TABLE B
inner join TABLE A on A.POLICY_ID=B.POLICY_ID

but I am not able to further join data for Table C . Also the records in TABLE B and C are different

Please advice

 

[synapsevampire]

Since you wan to go with a command object, which isn't required and doesn't allow for multiple parameters, try:



select
A.POLICY_NUM,
MyTable.ISSUE_DATE,
MyTable.DELETE_DATE,
MyTable.CANCEL_DATE
from TABLE1 A
inner join table2 B on A.POLICY_ID = B.POLICY_ID
inner join table3 C on B.POLICY_ID = C.POLICY_ID

You could also UNION ALL tables B and C and join that to A.

But there's no reason to use a Command anyway, use the Crystal gui and link A to B, and B to C.

This means that each rowwould have to have all of the links.

-k