An inner join would solve this.

OR...you can do this all in one table if you really want it to be simple.
It would also be recursive so you could nest records within other records
indefinitely.

=========================
parent child VALUE
0 1 Val1 --(could be) master record, top level
1 2 Val2 --parent is child = 1
1 3 Val3 --parent is child = 1
2 4 Val4 --parent is child = 2
3 5 Val5
4 6 Val6
=========================

Hope that makes sense. :-)

-v

_____

From: Martijn Tonies [mailto:m.tonies@...]
Sent: Friday, December 10, 2004 3:44 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Creating a view that relies on another
view... possible?


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



Yahoo! Groups Sponsor

ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=1290pt2fa/M=298184.5639630.6699735.3001176/D=gr
oups/S=1705115386:HM/EXP=1102761790/A=2434971/R=0/SIG=11eeoolb0/*http://www.
netflix.com/Default?mqso=60185400> click here

<http://us.adserver.yahoo.com/l?M=298184.5639630.6699735.3001176/D=groups/S=
:HM/A=2434971/rand=511768761>

_____

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]