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

I see you are one team !

Kara

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.