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.tysv...@kreftregisteret.no> > ** > > > 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] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org 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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
