Hello Clay,

> I have three tables (relative to my question) that hold data I want to

I read that as "three tables (relative of my position)". Figured it didn't
make sense. :-)

> 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

LEFT JOIN CLIENT_FUNDERS F1 ON (C.CLIENT_ID = F1.CLIENT_ID
AND F1.IS_PRIMARY_FUNDER = 1)

> JOIN CLIENT_FUNDERS F2 ON C.CLIENT_ID = F2.CLIENT_ID

LEFT JOIN CLIENT_FUNDERS F2 ON (C.CLIENT_ID = F2.CLIENT_ID
AND F2.IS_PRIMARY_FUNDER = 0)

> WHERE
>
> (REFERRAL_DATE IS NOT NULL) AND
>
> (ADMIT_DATE IS NULL) AND

Drop these:

> (F1.IS_PRIMARY_FUNDER = 1) AND
> (F2.IS_PRIMARY_FUNDER = 0)

Keep this:

> ORDER BY A.REFERRAL_DATE

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com