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.