Hello,

> Could somebody please tell me if the following is possible:
>
> I have two tables
>
> TABLE_A:
> =========================
> AKEY
> 1
> 2
> 3
> 4
> =========================
>
>
> TABLE_B:
> =========================
> BKEY AKEY VALUE
> 1 1 Val1
> 2 2 Val2
> 3 3 Val3
> 4 4 Val4
> 5 4 Val5
> 6 4 Val6
> =========================
> What I would like to do is this. Create a view on Table A that limits
> the records that can be seen by a user:
>
> CREATE VIEW MYVIEW AS SELECT * FROM TABLE_A WHERE AKEY=1 OR AKEY=4
>
> I would then like to create a View on B that will only allow the user
> to see the fields that are linked to Table A (via "AKEY") and that are
> visible in "MYVIEW"
>
> I was thinking along the lines of:
>
> CREATE VIEW "LIMITED_VIEW" AS SELECT VALUE FROM TABLE_A TABA WHERE
> (EXISTS (SELECT AKEY FROM MYVIEW TABB WHERE TABA.AKEY = TABB.BKEY))

Well, it's possible, so that can't be the problem.

Why not do a JOIN?

> The result should be:
> "LIMITED_VIEW"
> =========================
> AKEY BKEY VALUE
> 1 1 Val1
> 4 4 Val4
> 5 4 Val5
> 6 4 Val6
> =========================
>
>
> Unfortunately, this does not produce any records.
>
> The idea is that as TABLE_A changes, the records that "LIMITED_VIEW"
> can see also changes.
>
> Does anybody understand? ;o) Is what I am asking possible? If so, how?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com