Results 1 to 3 of 3

Thread: Left join

  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Post Unanswered: Left join

    Hello All,

    I have two tables, Names of the tables are T1, T2. Both tables having Cus_iD, Plan. I need to retrieve a CUS,PL from T1, where Cus should have 2 or more plans and The same customer shold have atleast one of plan of T1, other plan sholdn't in T2.

    I never used left or righter joins, but i am very comfortable with inner, Subseect, Exists and other functions,

    I am having difficulty to execute my query, i am not getting expected results.


    Please see the data below

    T1 ( Should have Altest 2 plans)

    CUS_ID Plan
    111 ST
    111 CT

    T2

    Cus_id Plan ( Should have 1 plan, the plan sholud be in T1)
    111 ST

    My result should be like

    CUS ID, Plan
    111 CT


    My Query is

    select CUs, PL
    FROm T1,

    Where date = 2009
    and option = y
    St_dt < end_dt
    and Pl not in ( select Pl from T2
    where
    a.cus id = b.cus id
    and a.pl = b.pl)

    Please help me thank you

  2. #2
    Join Date
    Jul 2009
    Posts
    150
    I see you are one team !

    Kara

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Why do you think you need LEFT OUTER JOINS? You can join T1 with itself using CUS_ID as join predicate. Then you have for each CUS every combination and can check that one plan in the joined row exists in T2 and the other doesn't.

    Since this smells like an exercise, you can figure out the details by yourself. With the above description, it should be straight-forward.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •