I think maybe a small change may help

left outer JOIN CLIENT_FUNDERS F1 ON C.CLIENT_ID = F1.CLIENT_ID

left outer JOIN CLIENT_FUNDERS F2 ON C.CLIENT_ID = F2.CLIENT_ID

----- Original Message -----
From: "Clay Shannon" <cshannon@...>
To: <firebird-support@yahoogroups.com>
Sent: Friday, September 16, 2005 9:13 AM
Subject: [firebird-support] I wish I were a SQL syntax guru


> (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]
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>