--------------------------------------------------------------------------------I have one table where I can do a SELECT * FROM schema.table1 where contact_type_id = 'Email1';and I get good results. Now I need to match up the contact_type_id with a member_number and a Client_numberI can trace the FK relationships from schema.table1 to the table that I want to join to in order to do my select for Contact_type_id, Member_number and Client_Number but I can't figure out how to prepare the SQL. Here is the DDL for the 4 tables. can any help me out? Create table FRATERNAL.CLIENT ( CLIENT_ID BIGINT NOT NULL , CLIENT_NUMBER CHARACTER(10) , COUNTRY_CD CHARACTER(2) , TYPE_ID CHARACTER(1) , IS_PERSON CHARACTER(1) , IS_SMOKER CHARACTER(1) , IS_SQUIRE CHARACTER(1) , CLASS_CODE CHARACTER(1) , CLASS_CODE_CHANGE_DT DATE , PREFERRED_LANG CHARACTER(2) NOT NULL , FIRST_NAME VARCHAR(32) NOT NULL , LAST_NAME VARCHAR(32) NOT NULL , MIDDLE_NAME VARCHAR(32) , ORG_NAME VARCHAR(128) , PREFIX_PROF VARCHAR(20) , ASSEMBLY_PREFIX_PROF VARCHAR(20) , PREFIX_FRAT VARCHAR(20) , ASSEMBLY_PREFIX_FRAT VARCHAR(20) , SUFFIX_FRAT VARCHAR(20) , ASSEMBLY_SUFFIX_FRAT VARCHAR(20) , SUFFIX_PROF VARCHAR(20) , ASSEMBLY_SUFFIX_PROF VARCHAR(20) , NICK_NAME VARCHAR(32) , EMPLOYER VARCHAR(50) , OCCUPATION VARCHAR(64) , TAX_ID BIGINT , BIRTH_DT DATE , CITZN_BY_BRTH CHARACTER(1) , DECEASED_DT DATE , ANNIV_DT DATE , GENDER CHARACTER(1) , SRC_ID DECIMAL(10,0) , ADD_NOTES VARCHAR(256) , USE_PRNT_ADDR_CD CHARACTER(1) , MARITAL_STATUS_CD CHARACTER(1) , CLI_1099_REQ_CD CHARACTER(1) , PROMINENT_SRC_CD CHARACTER(1) , PROMINENT_INDICATOR CHARACTER(1) , RECEIVE_EMAIL_NOTICE CHARACTER(1) , MARITAL_ANNIVERSARY_DATE DATE , HIERARCHY_TYPE CHARACTER(3) , HIERARCHY_TITLE VARCHAR(64) , ORDINATION_DT DATE , PARISH_NAME VARCHAR(128) , PARISH_NBR BIGINT , CREATED_BY VARCHAR(20) NOT NULL , CREATED_DT TIMESTAMP NOT NULL With Default CURRENT TIMESTAMP , MODIFIED_BY VARCHAR(20) , MODIFIED_DT TIMESTAMP ) in TSFRAT02 Data Capture Changes;---------------------------------------------------- Create Primary Key SQL080125200604460--------------------------------------------------alter table FRATERNAL.CLIENT add constraint SQL080125200604460 Primary Key (CLIENT_ID);Create table FRATERNAL.CLIENT_CONTACTS ( CLIENT_ID BIGINT NOT NULL , CONTACT_ID BIGINT NOT NULL ) in TSFRAT02 ;---------------------------------------------------- Create Primary Key SQL080125200657470--------------------------------------------------alter table FRATERNAL.CLIENT_CONTACTS add constraint SQL080125200657470 Primary Key (CLIENT_ID, CONTACT_ID);Create table FRATERNAL.CONTACTS ( CONTACT_ID BIGINT NOT NULL , CONTACT_TYPE_ID VARCHAR(10) NOT NULL , CONTACT_INFO VARCHAR(128) NOT NULL , CONTACT_EXT VARCHAR(10) , CONTACT_STATUS CHARACTER(1) , CREATED_BY VARCHAR(20) NOT NULL , CREATED_DT TIMESTAMP NOT NULL With Default CURRENT TIMESTAMP , MODIFIED_BY VARCHAR(20) , MODIFIED_DT TIMESTAMP , HISTORY_YN CHARACTER(1) ) in TSFRAT02 Data Capture Changes;---------------------------------------------------- Create Primary Key SQL080125200603750--------------------------------------------------alter table FRATERNAL.CONTACTS add constraint SQL080125200603750 Primary Key (CONTACT_ID);Create table FRATERNAL.MEMBER ( CLIENT_ID BIGINT NOT NULL , MEMBER_NUMBER DECIMAL(8,0) NOT NULL , COUNCIL_ORG_ID BIGINT NOT NULL , ASSEMBLY_ORG_ID BIGINT , FIRST_DEGREE_DT DATE NOT NULL , SECOND_DEGREE_DT DATE , SECOND_DEGREE_IND CHARACTER(1) , THIRD_DEGREE_DT DATE , THIRD_DEGREE_IND CHARACTER(1) , FOURTH_DEGREE_DT DATE , COUNCIL_REENTRY_DATE DATE , ASSEMBLY_REENTRY_DATE DATE , F1845_RCPT_DATE DATE , FORMER_SQUIRE_CD CHARACTER(1) , DISABILITY_CD CHARACTER(1) , CLASS_CD CHARACTER(1) , SUB_CLASS_CD CHARACTER(1) , CLASS_ID BIGINT , ASSEMBLY_CLASS_CD CHARACTER(1) , TYPE_CD CHARACTER(1) , TYPE_CD_EXIT_DATE DATE , HAS_REGALIA CHARACTER(1) , FOURTH_DEGREE_STATUS CHARACTER(1) , BILLING_STATUS CHARACTER(1) , BILLING_STAT_CHANGE_DATE DATE , ASSEMBLY_BILLING_STATUS CHARACTER(1) , ASSEMBLY_BILLING_STAT_CHG_DATE DATE , PRINT_BILLS_YN CHARACTER(1) , ASSEMBLY_PRINT_BILLS_YN CHARACTER(1) , ASSESS_YN CHARACTER(1) , ASSEMBLY_ASSESS_YN CHARACTER(1) , DUES_PAID_THRU DATE , ASSEMBLY_DUES_PAID_THRU DATE , COUNCIL_SUSPENDED_AMT DECIMAL(9,2) , COUNCIL_SUSPENDED_CHANGE_DATE DATE , ASSEMBLY_SUSPENDED_AMT DECIMAL(9,2) , ASSEMBLY_SUSPENDED_CHANGE_DATE DATE , BILLING_FREQ CHARACTER(1) , ASSEMBLY_BILLING_FREQ CHARACTER(1) , DELIVERY_PREFERENCE CHARACTER(1) , CREATED_BY VARCHAR(20) NOT NULL , CREATED_DT TIMESTAMP NOT NULL With Default CURRENT TIMESTAMP , MODIFIED_BY VARCHAR(20) , MODIFIED_DT TIMESTAMP , SUB_CLASS_ID BIGINT , ASSEMBLY_SUB_CLASS_ID BIGINT , ASSEMBLY_DELIVERY_PREFERENCE CHARACTER(1) ) in TSFRAT02 Data Capture Changes;---------------------------------------------------- Create Primary Key SQL080930044653800--------------------------------------------------alter table FRATERNAL.MEMBER add constraint SQL080930044653800 Primary Key (CLIENT_ID);---------------------------------------------------- Create Foreign Key FRATERNAL.SQL071026134156780--------------------------------------------------alter table FRATERNAL.CLIENT_CONTACTS add constraint SQL071026134156780 foreign key (CLIENT_ID) references FRATERNAL.CLIENT (CLIENT_ID) On Delete Cascade On Update No Action;---------------------------------------------------- Create Foreign Key FRATERNAL.SQL071026134157040--------------------------------------------------alter table FRATERNAL.CLIENT_CONTACTS add constraint SQL071026134157040 foreign key (CONTACT_ID) references FRATERNAL.CONTACTS (CONTACT_ID) On Delete Cascade On Update No Action;---------------------------------------------------- Create Foreign Key FRATERNAL.SQL070619101509330--------------------------------------------------alter table FRATERNAL.CONTACTS add constraint SQL070619101509330 foreign key (CONTACT_TYPE_ID) references FRATERNAL.CONTACT_TYPE (CONTACT_TYPE_ID) On Delete No Action On Update No Action;---------------------------------------------------- Create Foreign Key FRATERNAL.SQL071026121436890--------------------------------------------------alter table FRATERNAL.MEMBER add constraint SQL071026121436890 foreign key (CLIENT_ID) references FRATERNAL.CLIENT (CLIENT_ID) On Delete No Action On Update No Action;
4 way join??
Posted - 2010-01-05 : 09:50:26
