Where-statement [message #22521] |
Tue, 15 October 2002 22:27 |
Karri
Messages: 33 Registered: May 2001
|
Member |
|
|
Hi
I need help for this:
I have two tables:
PERMITED_TAB which include columns USER_ID and PERMITTED_NO
ORDER_TAB which include some columns and USER_ID.
exm:
PERMITED_TAB
USER_ID PERMITTED_NO
1 100
1 200
1 300
2 100
3 200
ORDER_TAB
ORDER_ID USER_ID and so.
3000 1 (permitted to 100, 200, 300)
3001 2 (permitted to 100)
3002 3 (permitted to 200)
Now I need that kind of WHERE-statement that,
if I am logged to the system in (USER_ID=2)
I can see all rows in ORDER_TAB where
USER_ID =2 is permitted. So in this case
USER_ID =2 can only see those rows which
are done by USER_ID = 1 or 2
ANd USER_ID =3 can see only those rows
which are done by USER_ID = 1 or 3
BR Karri
|
|
|
Re: Where-statement [message #22525 is a reply to message #22521] |
Wed, 16 October 2002 03:01 |
CADixit
Messages: 11 Registered: September 2002
|
Junior Member |
|
|
Try this..
select p.USER_ID, p.PERMITTED_NO, o.ORDER_ID
from PERMITED_TAB p, ORDER_TAB o
where p.PERMITTED_NO in (select p1.PERMITTED_NO
from PERMITED_TAB p1
where p1.USER_ID=SYS_CONTEXT ('USERENV', 'CURRENT_USERID'))
and o.USER_ID = p.USER_ID
|
|
|
|
|