Try this query, it works on FB2.5

SELECT
CLIENT_GROUPS.CODE,
CASE WHEN GROUPING.CLIENT_CODE IS NULL THEN 0 ELSE 1 END LINKED
FROM CLIENT_GROUPS
LEFT JOIN (
SELECT
CLIENT_GROUPING.*
FROM CLIENT_GROUPING
WHERE CLIENT_GROUPING.CLIENT_CODE = 'Mary'
) GROUPING ON GROUPING.GROUPING_CODE = CLIENT_GROUPS.CODE

On Mon, Aug 1, 2011 at 7:54 AM, cornievs <cornievs@...> wrote:

> **
>
>
> I have 2 tables that I need to join with a third table defining a linked
> between the 2.
>
> CLIENTS A
>
> CODE VARCHAR(15) (PK)
> NAME VARCHAR(30)
> .
> .
>
> CLIENT_GROUPS B
> CODE VARCHAR(15) (PK)
> DESCRIPTION VARCHAR(30)
>
> CLIENT_GROUPING C
> GROUPING_CODE (PK) (Links to CODE in B CLIENT_GROUP)
> CLIENT_CODE (PK) (LINKS to CODE in A CLIENTS)
>
> What I need is SELECT result with each and everyone record of B
> CLIENT_GROUPS only once, plus a FIELD (call it LINKED) which indicates if
> there is record in CLIENT_GROUPINGS where C.GROUPING_CODE = B.CODE and
> C.CLIENT_CODE = 'xyz client'
>
> Example Data
>
> CLIENTS
> CODE NAME
> John John Murray
> Mary Mary Poppins
>
> CLIENT_GROUPS
> CODE DESCRIPTION
> Every Everyone
> OT18 Older Than 18
>
> CLIENT_GROUPINGS
> GROUPING_CODE CLIENT_CODE
> Every John
> Every Mary
> OT18 John
>
> Required result when C.CLIENT_CODE = 'Mary'
>
> GROUP_CODE LINKED
> Every True or 1 or not null
> OT18 False or 0 or null
>
> Any help will be much appreciated.
>
> Regards
>
> Cornie van Schoor
>
>
>



--
Iwan Cahyadi Sugeng


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