Hi Michel,

Try,

SELECT menu.* FROM menu  ;
	JOIN MenuOptions mo ON mo.NoMenu = menu.NoMenu AND mo.Enabled ;
UNION ;
SELECT menu.* FROM menu  ;
	JOIN MenuPriviledge mp ON mp.NoMenu = menu.NoMenu AND mp.notype = lnNotype ;	
	LEFT JOIN MenuOptions mo ON mo.NoMenu = menu.NoMenu  ;
	WHERE mo.Enabled IS NULL

>I am looking for a way to achieve a complex SQL. I have the following tables:
>
>Menu.dbf
>
>List of menu items to be shown to the user
>
>MenuPriviledge.dbf
>
>A one to many relation from Menu.dbf containing several records per Menu.dbf identifying a type of access. So, if I have an item named "Update your profile" with the primary key 28, in this table, I may have:
>
>

>Primary key   NoMenu     NoType
>1             28         1
>2             28         2
>3             28         3
>

>
>So, basically, this would mean that this menu item is available for all users having a NoType of 1, 2 or 3.
>
>MenuOptions.dbf
>
>This is another way to define priviledges to the menu items. Basically, MenuPriviledge.dbf is the first level of access. But, if some definitions are done in MenuOptions.dbf, then those ones would have priority. So, I can have records like this:
>
>

>Primary key   NoUser     NoMenu     Enabled
>1             1          28         True
>2             1          29         False
>3             4          28         True
>4             7          32         False
>

>
>So, basically, this would mean that user having the primary key 1 is allowed to access the menu having the primary key 28. So, even if MenuPriviledge.dbf does not contain access to that menu for this user, he still can access it because we have defined an exception in MenuOptions.dbf.
>
>In the second record, it means that even if that user is having access to Menu 29 in MenuPriviledge.dbf, because we have defined an exception in MenuOptions.dbf to False for that menu, he should not be able to access it.
>
>So, the goal is to obtain a SQL result with only valid menu items. That can then be used to display the menu.