Re: Sub query base on security table

"beerora" <beersa.beersa_at_gmail.com> a écrit dans le message de news: 1161757404.344874.259580_at_m7g2000cwm.googlegroups.com...

Michel Cadot wrote:
> <beersa.beersa_at_gmail.com> a écrit dans le message de news: 1161746806.526394.172070_at_h48g2000cwc.googlegroups.com...
> |I am using oracle9i.
> | I am seeking tips on following:
> | I have a table with million rows. I want to restrict the rows by
> | user_id.
> | Eg.,
> | The main table has following structure:
> | tbl_a (year,entity,dept,bal)
> |
> | And the security table has following structure:
> | tbl_s(login_id,entity1,entity2,entity3)
> |
> | The rows in main table is restricted based on the values in security
> | table.
> | E.g,
> | the values in main table are as:
> | 2005,A,HR,200
> | 2005,B,IT,100
> | 2006,A,HR,20
> | The values in security table are as:
> | abc,A,C,E
> | efg,B,D,F
> | xyz,C,H,K
> |
> | The user 'abc' can only allow to see the rows that 'entity' in the main
> | table should match with 'entity1' or
> | 'entity2' or 'entity3' in the security table
> |
> | I write query as:
> | SELECT * from tbl_a where exists
> | (SELECT * from tbl_s where login_id=USER)
> |
> | but it returns all the rows without restriction.
> |
> | Your advice and tips are highly appreciated.
> |
> | Thks.
> |
>
> A condition on entity* is missing:
>
> SELECT * from tbl_a where exists
> (SELECT 1 from tbl_s where login_id=USER
> and tbl_a.entity in (tbl_b.entity1,tbl_b.entity2,tbl_b.entity3)
> )
> /

Thx Mike.

Any other approach instead of typing the tbl_b.enitity* one by one in IN clause?

If you have N columns, how could you get the data from these columns without naming them?

Regards
Michel Cadot Received on Wed Oct 25 2006 - 10:45:31 CDT