October 6, 2010 at 12:19 am
I am using sql server 2000. Below is the scenario:
I have 3 tables:
select * from A1;
gives:
agentid, actid
1, 100
select * from B1;
gives:
actid, restid
100, 200
100, 202
select * from C1;
gives:
restid, plancode
200, AB
201, AC
202, AD
With the given information, we see that agentid: 1 have account id 100 and restricted with 200 and 202. We need a query, such that it finds the restricted id (restid) which is not included for this agent and its plan code.
i.e., i need a query such that it displays
agentid, restid, plancode
1, 201, AC
With this above output, we understand that agent is not restricted to use the plan code AC.
Can u please help!. It is sql server 2000.
Thanks
Premkumar
I need a query such that, agent
October 6, 2010 at 2:27 am
SELECT a.agentid,c.restid,c.plancode
from #a1 a, #c1 c
where c.restid not in (select restid from #b1)
----------
Ashish
October 6, 2010 at 2:37 am
Ashish,
Thanks. it works. But if add another record in table A1, it does not work as expected:
select * from A1;
gives:
agentid, actid
1, 100
2, 200
In this case,
agent 1 have plancode AC,
but agent 2, do not have restriction, So Agent 2 must have all plan codes displayed. Need help
October 6, 2010 at 2:48 am
Finally, i found the query.
Thanks Ashish. Great support.
below is the query:
SELECT a.agentid,a.actid,c.restid,c.plancode
from a1 a, c1 c
where c.restid not in (select restid from b1) or a.actid not in (select actid from b1)
group by agentid,a.actid,c.restid,c.plancode
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply