Re: SQL question regarding a one to many relationship

Hi Mike,
You need to code your SQL "where" clause to indicate an outer join.

Modify the where clause to look like:

SW_TRACKING."SEQ_NO" = SW_HWENF."SEQ_NO(+)" This will include all rows in SW_TRACKING even if no corresponding rows exist in SW_HWENF.

Regards, Tim

Mike Ellenbecker wrote:

> I have a table called SW_TRACKING (one relationship) linked to SW_HWENF(many
> relationship). The problem I have is that when there is no record
> associated with the SW_HWENF table, the SW_TRACKING record does not show up.
> Here is the SQL statement that is used to generate the report.
>
> **********************************************************
> SELECT
> SW_TRACKING."SEQ_NO", SW_TRACKING."FACILITY_NAME", SW_TRACKING."FID",
> SW_TRACKING."CITY", SW_TRACKING."CONTACT_DATE",
> SW_TRACKING."LEAD_INVESTIGATOR_ID", SW_TRACKING."COUNTY_CODE",
> SW_TRACKING."GRANT_COMMITMENT_TEXT",
> SW_TRACKING."SITE_INVEST_LEAD_PROGRAM_CODE",
> SW_HWENF."ENFORCEMENT_LETTER_SENT_DATE", SW_HWENF."ENFORCEMENT_ACTION_TYPE",
> SW_HWENF."ENFORCEMENT_ACTION_CLOSED_DATE",
> SW_HWENF."CONTACT_LETTER_DAY_COUNT", SW_HWENF."VIOLATION_CLOSED_DAY_COUNT"
> FROM
> "S71585"."SW_TRACKING" SW_TRACKING,
> "S71585"."SW_HWENF" SW_HWENF
> WHERE
> SW_TRACKING."SEQ_NO" = SW_HWENF."SEQ_NO"
> ORDER BY
> SW_TRACKING."LEAD_INVESTIGATOR_ID" ASC,
> SW_TRACKING."CONTACT_DATE" ASC
>
> **********************************************************
>
> My guess is that I need to included a statement like this
>
> where exists (select null
> from SW_HWENF
> where ??????)
>
> after the where statement in the above SQL statement
>
> Thank in advance
> Mike Ellenbecker
Received on Thu Aug 12 1999 - 16:09:12 CDT