(as opposed to an Oscar Meyer Weiner).



I have three tables (relative to my question) that hold data I want to
display in a grid (people who have been referred but not as of yet
admitted).



In every case, two of the tables (CLIENTS and CLIENT_ADMISSIONS) will hold
related data. The third table (CLIENT_FUNDERS) may or may not contain
related data. CLIENT_FUNDERS has a pseudo-boolean smallint column named
IS_PRIMARY_FUNDER.



There may be a Primary funder but no secondary funder, or no funder at all.



I have tried left joins to no avail, and right joins to even worse than no
(adverse) avail (hang/lockup). Here is the SQL in question:



SELECT distinct (C.CLIENT_ID),

C.FIRSTNAME || ' ' || C.MIDDLENAME || ' ' || C.LASTNAME AS "NAME",

'Edit Case' as "EDIT CASE",

'Notes' as "NOTES",

A.REFERRAL_DATE, A.TARGET_ADMIT_DATE, A.ASSIGNEE,

C.DOB,

F1.FUNDER_NAME AS "PRI. FUNDING",

F2.FUNDER_NAME AS "SEC. FUNDING"

FROM CLIENT_ADMISSIONS A

JOIN CLIENTS C ON C.CLIENT_ID = A.ADMISSION_ID

JOIN CLIENT_FUNDERS F1 ON C.CLIENT_ID = F1.CLIENT_ID

JOIN CLIENT_FUNDERS F2 ON C.CLIENT_ID = F2.CLIENT_ID

WHERE

(REFERRAL_DATE IS NOT NULL) AND

(ADMIT_DATE IS NULL) AND

(F1.IS_PRIMARY_FUNDER = 1) AND

(F2.IS_PRIMARY_FUNDER = 0)

ORDER BY A.REFERRAL_DATE



How must I change this magnificently beautiful but inaccurate/incomplete SQL
to still show data even when there are no corresponding records in the
CLIENT_FUNDERS table?



Clay Shannon,

Dimension 4 Software





[Non-text portions of this message have been removed]