Cornie van Schoor 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 answered:

>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

Why the subselect? Wouldn't

SELECT B.CODE, CASE WHEN C.CLIENT_CODE IS NULL THEN 0 ELSE 1 END AS LINKED
FROM CLIENT_GROUPS B
LEFT JOIN CLIENT_GROUPING C
ON B.CODE = C.GROUPING_CODE
AND C.CLIENT_CODE = 'Mary'

be a simpler option? I expect 'Mary' to appear 0 or 1 time in CLIENT_GROUPING, if CLIENT_GROUPING can contain duplicates, some changes have to be done.

HTH,
Set