Nope, you need to do an outer join, in this case a right outer join.
Basically that means that Oracle will return a record will all columns
containing NULL in SW_HWENF when there isn't a matching record in
SW_TRACKING.
To do this, modify the WHERE clause of your query as follows:
WHERE
SW_TRACKING."SEQ_NO" = SW_HWENF."SEQ_NO"(+)
Mike Ellenbecker <ellenm_at_mail01.dnr.state.wi.us> wrote in message
news:7onbin$b71_at_Musky.state.wi.us...
> 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:18:32 CDT
