G
Guest
I have two tables that I need to perform a query on, but my query misses some
data.
Table 1) named TST_FR_CASE_RECORDS, has a two part primary key field. 1st
field is: CASE_NUM_YR and 2nd field is CASE_NUM
Table 2) named TST_FR_CASE_OTHERS, has the same primary key field layout.
1st field is: CASE_NUM_YR and 2nd field is CASE_NUM. There is a secondary
index to this table (field # 3) SEQ_NUM.
Table 1 is the Parent Table, Table 2 is the child table. There is a
possibility that there may never be a matching record in table 2 for a record
in table 1. There is always a possiblity that there may be more than 1
records in table 2 that links to table 1, therefore a one-to-many condition
exists.
My query written as follows, produces a recordset results that lists all
records of table 1 that have a matching record(s) in table 2. I need this
query to have a result of all records in table 1 included, regardless if
there is a matching record in table 2. Can someone assist in helping me to
correct this query.
Query Follows:
SELECT TST_FR_CASE_RECORDS.CASE_NUM_YR, TST_FR_CASE_RECORDS.CASE_NUM,
TST_FR_CASE_RECORDS.PRTD_CDE, TST_FR_CASE_RECORDS.TYPIST_INIT_TXT,
TST_FR_CASE_RECORDS.ACC_DATE, TST_FR_CASE_RECORDS.CNTY_NUM,
TST_FR_CASE_RECORDS.HRG_DATE, TST_FR_CASE_RECORDS.HRG_TIME_TXT,
TST_FR_CASE_RECORDS.HRG_AM_PM_TXT, TST_FR_CASE_RECORDS.LOC_CDE,
TST_FR_CASE_RECORDS.ATTY_NUM, TST_FR_CASE_RECORDS.LIC_FIRST_NME,
TST_FR_CASE_RECORDS.LIC_MIDDLE_NME, TST_FR_CASE_RECORDS.LIC_LAST_NME,
TST_FR_CASE_RECORDS.LIC_SUBT_TXT, TST_FR_CASE_RECORDS.LIC_ADDR_TXT,
TST_FR_CASE_RECORDS.LIC_CITY_NME, TST_FR_CASE_RECORDS.LIC_STATE_CDE,
TST_FR_CASE_RECORDS.LIC_ZIP_CDE, TST_FR_CASE_RECORDS.DOA_NME,
TST_FR_CASE_RECORDS.DOA_ADDR_TXT, TST_FR_CASE_RECORDS.DOA_CITY_NME,
TST_FR_CASE_RECORDS.DOA_STATE_CDE, TST_FR_CASE_RECORDS.DOA_ZIP_CDE,
TST_FR_CASE_RECORDS.FLAG_CDE, TST_FR_CASE_RECORDS.BATCH_DATE,
TST_FR_CASE_RECORDS.BATCH_NUM, TST_FR_CASE_OTHERS.OTHER_NME,
TST_FR_CASE_OTHERS.FIRM_NME, TST_FR_CASE_OTHERS.OTHER_ADDR_TXT,
TST_FR_CASE_OTHERS.OTHER_CITY_NME, TST_FR_CASE_OTHERS.OTHER_STATE_CDE,
TST_FR_CASE_OTHERS.OTHER_ZIP_CDE, TST_FR_HEARING_LOC.LOC_NME,
TST_FR_HEARING_LOC.ROOM_NME, TST_FR_HEARING_LOC.MEMO_TXT,
TST_FR_ATTORNEY.FIRST_NME, TST_FR_ATTORNEY.MIDDLE_NME,
TST_FR_ATTORNEY.LAST_NME, TST_FR_ATTORNEY.SUBTITLE_TXT,
TST_FR_ATTORNEY.FIRM_NME, TST_FR_ATTORNEY.ADDR1_TXT,
TST_FR_ATTORNEY.ADDR2_TXT, TST_FR_ATTORNEY.CITY_NME,
TST_FR_ATTORNEY.STATE_CDE, TST_FR_ATTORNEY.ZIP_CDE
FROM ((TST_FR_CASE_RECORDS INNER JOIN TST_FR_ATTORNEY ON
TST_FR_CASE_RECORDS.ATTY_NUM = TST_FR_ATTORNEY.ATTY_NUM) INNER JOIN
TST_FR_CASE_OTHERS ON (TST_FR_CASE_RECORDS.CASE_NUM =
TST_FR_CASE_OTHERS.CASE_NUM) AND (TST_FR_CASE_RECORDS.CASE_NUM_YR =
TST_FR_CASE_OTHERS.CASE_NUM_YR)) INNER JOIN TST_FR_HEARING_LOC ON
TST_FR_CASE_RECORDS.LOC_CDE = TST_FR_HEARING_LOC.LOC_CDE
ORDER BY TST_FR_CASE_RECORDS.CASE_NUM_YR;
Thanks
data.
Table 1) named TST_FR_CASE_RECORDS, has a two part primary key field. 1st
field is: CASE_NUM_YR and 2nd field is CASE_NUM
Table 2) named TST_FR_CASE_OTHERS, has the same primary key field layout.
1st field is: CASE_NUM_YR and 2nd field is CASE_NUM. There is a secondary
index to this table (field # 3) SEQ_NUM.
Table 1 is the Parent Table, Table 2 is the child table. There is a
possibility that there may never be a matching record in table 2 for a record
in table 1. There is always a possiblity that there may be more than 1
records in table 2 that links to table 1, therefore a one-to-many condition
exists.
My query written as follows, produces a recordset results that lists all
records of table 1 that have a matching record(s) in table 2. I need this
query to have a result of all records in table 1 included, regardless if
there is a matching record in table 2. Can someone assist in helping me to
correct this query.
Query Follows:
SELECT TST_FR_CASE_RECORDS.CASE_NUM_YR, TST_FR_CASE_RECORDS.CASE_NUM,
TST_FR_CASE_RECORDS.PRTD_CDE, TST_FR_CASE_RECORDS.TYPIST_INIT_TXT,
TST_FR_CASE_RECORDS.ACC_DATE, TST_FR_CASE_RECORDS.CNTY_NUM,
TST_FR_CASE_RECORDS.HRG_DATE, TST_FR_CASE_RECORDS.HRG_TIME_TXT,
TST_FR_CASE_RECORDS.HRG_AM_PM_TXT, TST_FR_CASE_RECORDS.LOC_CDE,
TST_FR_CASE_RECORDS.ATTY_NUM, TST_FR_CASE_RECORDS.LIC_FIRST_NME,
TST_FR_CASE_RECORDS.LIC_MIDDLE_NME, TST_FR_CASE_RECORDS.LIC_LAST_NME,
TST_FR_CASE_RECORDS.LIC_SUBT_TXT, TST_FR_CASE_RECORDS.LIC_ADDR_TXT,
TST_FR_CASE_RECORDS.LIC_CITY_NME, TST_FR_CASE_RECORDS.LIC_STATE_CDE,
TST_FR_CASE_RECORDS.LIC_ZIP_CDE, TST_FR_CASE_RECORDS.DOA_NME,
TST_FR_CASE_RECORDS.DOA_ADDR_TXT, TST_FR_CASE_RECORDS.DOA_CITY_NME,
TST_FR_CASE_RECORDS.DOA_STATE_CDE, TST_FR_CASE_RECORDS.DOA_ZIP_CDE,
TST_FR_CASE_RECORDS.FLAG_CDE, TST_FR_CASE_RECORDS.BATCH_DATE,
TST_FR_CASE_RECORDS.BATCH_NUM, TST_FR_CASE_OTHERS.OTHER_NME,
TST_FR_CASE_OTHERS.FIRM_NME, TST_FR_CASE_OTHERS.OTHER_ADDR_TXT,
TST_FR_CASE_OTHERS.OTHER_CITY_NME, TST_FR_CASE_OTHERS.OTHER_STATE_CDE,
TST_FR_CASE_OTHERS.OTHER_ZIP_CDE, TST_FR_HEARING_LOC.LOC_NME,
TST_FR_HEARING_LOC.ROOM_NME, TST_FR_HEARING_LOC.MEMO_TXT,
TST_FR_ATTORNEY.FIRST_NME, TST_FR_ATTORNEY.MIDDLE_NME,
TST_FR_ATTORNEY.LAST_NME, TST_FR_ATTORNEY.SUBTITLE_TXT,
TST_FR_ATTORNEY.FIRM_NME, TST_FR_ATTORNEY.ADDR1_TXT,
TST_FR_ATTORNEY.ADDR2_TXT, TST_FR_ATTORNEY.CITY_NME,
TST_FR_ATTORNEY.STATE_CDE, TST_FR_ATTORNEY.ZIP_CDE
FROM ((TST_FR_CASE_RECORDS INNER JOIN TST_FR_ATTORNEY ON
TST_FR_CASE_RECORDS.ATTY_NUM = TST_FR_ATTORNEY.ATTY_NUM) INNER JOIN
TST_FR_CASE_OTHERS ON (TST_FR_CASE_RECORDS.CASE_NUM =
TST_FR_CASE_OTHERS.CASE_NUM) AND (TST_FR_CASE_RECORDS.CASE_NUM_YR =
TST_FR_CASE_OTHERS.CASE_NUM_YR)) INNER JOIN TST_FR_HEARING_LOC ON
TST_FR_CASE_RECORDS.LOC_CDE = TST_FR_HEARING_LOC.LOC_CDE
ORDER BY TST_FR_CASE_RECORDS.CASE_NUM_YR;
Thanks