I want to do this SQL but in a Filter Property of a Table component.

SELECT * FROM VideoList
WHERE EXISTS (SELECT * FROM ActorVideos WHERE ActorName = 'James Bond' and VideoHash = VideoList.Hash)

VideoList is a table with all films. ActorVideos is a table with the actor names and the videos with that actor. Any single video can have multiple Actors. So I want get all records from VideoList where the Actor is part of it.

So I tried to set the Filter to:
"EXISTS (SELECT * FROM ActorVideos where ActorName = 'Katt Dylan' and VideoHash = VideoList.Hash)"

This doesn't work, as it seems there is no way to use a Subselect in the Filter. Any ideas how I cant get my results with a table compontent and Filter instead a Query component? Change it to a query would need some heavy rework of my rountines and I want try to prevent this.

Rolf


Simple answer is - you can't

The only way I've found to do something like this is to apply a "static" filter in a two stage process.

1. Generate a list of IDs for the target table using a query
2. Set a filter like ID IN (generated list)

The other way you could cheat is to add an extra column to the VideoList table and use a trigger to populate it with a boolean

Roy Lambert

Rolf,

Perhaps a VIEW could do what you want.

Create a VIEW joining VideoList and ActorVideo selecting VideoList.*, ActorVideos.ActorName WHERE VideoList.Hash = ActorVideos.VideoHash

This should allow you to do the filtering.

Richard

Richard


Nice idea - stored for future use

Roy Lambert

Rolf

Please try using the following: It works fine for me:

SELECT * FROM VideoList
WHERE VideoList.Hash IN
(SELECT VideoHas FROM ActorVideos WHERE ActorName = 'James Bond')

Adam

--

NOTE: The "SELECT IN (<subselect>)" syntax is very useful. The field(s) returned by the subselect must be the same type(s) as the field(s) in the WHERE. The WHERE can consist of multiple comma-separated fields.

Adam


I think you missed the bit where Rolf says he's trying to use it as a table filter

Roy Lambert

@Richard Harding
The problem is that the table should be editable, as I must do some updates on it. So a view will not work.

I have now done it with a Query, where I than slect the actual record of the Query in the Table component. Some type of Master/Detail, where my master ist the Query and the Detail is the Table.

Thanks anyway for all your tips.

Rolf,

<< The problem is that the table should be editable, as I must do some updates on it. So a view will not work. >>

Views are editable as long as they obey the rules for generating a sensitive result set.  You can see whether a view is updateable or not in the EDB Manager by looking at the Properties/Details area for the view.  There you will see a Updateable: Yes/No that indicates this.

Tim Young
Elevate Software
www.elevatesoft.com