Hahaha .. My Mistake, i've been trying all possible query,
and haven't optimize it yet and send it right away. Good point, thanks

2011/8/1 Svein Erling Tysv�r <svein.erling.tysvaer@...>

> **
>
>
> 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
>
>
>

--
Iwan Cahyadi Sugeng


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